Saturday 5 September 2015

Your Excel formulas sheet: 15 tips for calculations and common tasks

Many of us fell in love with Excel as we delved into its deep and sophisticated formula features. Because there are multiple ways to get results, you can decide which method works best for you. For example, there are several ways to enter formulas and calculate numbers in Excel.


1. Manually enter Excel formulas:


Long Lists: =SUM(B4:B13)

Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.

Excel formulas







2. Click the Insert Function button


Use the Insert Function button under the Formulas tab to select a function from Excel’s menu list:

=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).

=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).


3. Select a function from a group (Formulas tab)


Narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time, for example.

=TODAY() Inserts today’s date.


4. The Recently Used button


Click the Recently Used button to show functions you've used recently. It's a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.

=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.


5. Auto functions under the AutoSum button


Auto functions are my editor's personal favorite, because they're so fast. Select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:

=MAX(B4:B13) returns the highest value in the list.

=MIN(B4:B13) returns the lowest value in the list.


AutoSum in ExcelJD SARTAIN

Use the AutoSum button to calculate basic formulas such as SUM, AVERAGE, COUNT, etc.


Note: If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.



Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.



Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.



Five handy formulas for common tasks


The five formulas below may have somewhat inscrutable names, but their functions save time and data entry on a daily basis.


Note: Some formulas require you to input the single cell or range address of the values or text you want calculated. When Excel displays the various cell/range dialog boxes, you can either manually enter the cell/range address, or cursor and point to it. Pointing means you click the field box first, then click the corresponding cell over in the worksheet. Repeat this process for formulas that calculate a range of cells (e.g., beginning date, ending date, etc.)



1. =DAYS


This is a handy formula to calculate the number of days between two dates (so there’s no worries about how many days are in each month of the range).

Example: End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days

Formula: =DAYS(A30,A29)


2. =NETWORKDAYS


This similar formula calculates the number of workdays (i.e., a five-day workweek) within a specified timeframe. It also includes an option to subtract the holidays from the total, but this must be entered as a range of dates.

Example: Start Date March 31, 2015 minus End Date October 12, 2015 = 140 days

Formula: =NETWORKDAYS(A33,A34)


3. =TRIM


TRIM is a lifesaver if you’re always importing or pasting text into Excel (such as from a database, website, word processing software, or other text-based program). So often, the imported text is filled with extra spaces scattered throughout the list. TRIM removes the extra spaces in seconds. In this case, just enter the formula once, then copy it down to the end of the list.

Example: =TRIM plus the cell address inside parenthesis.

Formula: =TRIM(A39)


Excel formulas


4. =CONCATENATE


This is another keeper if you import a lot of data into Excel. This formula joins (or merges) the contents of two or more fields/cells into one. For example: In databases; dates, times, phone numbers, and other multiple data records are often entered in separate fields, which is a real inconvenience. To add spaces between words or punctuation between fields, just surround this data with quotation marks.

Example: =CONCATENATE plus (month,”space”,day,”comma space”,year) where month, day, and year are cell addresses and the info inside the quotation marks is actually a space and a comma.

Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)

Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)


5. =DATEVALUE


DATEVALUE converts the above formula into an Excel date, which is necessary if you plan to use this date for calculations. This one is easy: Select DATEVALUE from the formula list. Click the Date_Text field in the dialog box, click the corresponding cell on the spreadsheet, then click OK, and copy down. The results are Excel serial numbers, so you must choose Format>Format Cells>Number>Date, and then select a format from the list.

Formula: =DATEVALUE(H33)


Three more formula tips


As you work with formulas more, keep these bonus tips in mind to avoid confusion:


Tip 1: You don’t need another formula to convert formulas to text or numbers. Just copy the range of formulas and then paste as Special>Values. Why bother to convert the formulas to values? Because you can’t move or manipulate the data until it’s converted. Those cells may look like phone numbers, but they’re actually formulas, which cannot be edited as numbers or text.



Tip 2: If you use Copy and Paste>Special>Values for dates, the result will be text and cannot be converted to a real date. Dates require the DATEVALUE formula to function as actual dates.



Tip 3: Formulas are always displayed in uppercase; however, if you type them in lowercase, Excel converts them to uppercase. Also notice there are no spaces in formulas. If your formula fails, check for spaces and remove them.


Excel formulas

SSC Combined Graduate Level (CGL) (Tier-II) 2014 Final Result Out



SSC Combined Graduate Level (CGL) (Tier-II) 2014 Final Result Out :
Date
Examination name and year
Write Up
Result
Marks
2nd September 2015
Combined Graduate Level Examination, 2014 - declaration of final result.LIST OF THE CANDIDATES IN ROLLNO. ORDER RECOMMENDED FOR APPOINTMENT FOR INTERVIEW POSTS OTHER THAN STATISTICAL INVESTIGATOR
2nd September 2015
Combined Graduate Level Examination, 2014 - declaration of final result.LIST OF THE CANDIDATES IN ROLLNO. ORDER RECOMMENDED FOR APPOINTMENTFOR THE POST OF STATISTICAL INVESTIGATOR GR.II
2nd September 2015
Combined Graduate Level Examination, 2014 - declaration of final result.LIST OF THE CANDIDATES IN ROLLNO. ORDER RECOMMENDED FOR APPOINTMENTFOR NON-INTERVIEW POSTS

Pradhan Mantri Jeevan Jyoti Bima Yojana (PMJJBY) and Pradhan Mantri Suraksha Bima Yojana (PMSBY) are going to be launched in all the CBS Post Offices w.e.f. 7th September, 2015

Guidelines for Grant of Reward to Informants leading to Recovery of Irrecoverable Taxes, 2015: CBDT Instruction No.07

Guidelines for Grant of Reward to Informants leading to Recovery of Irrecoverable Taxes, 2015
Instruction No.07/2015
F .No.385/21/2015-IT(B)
Government of India
Ministry of Finance
Department of Revenue
Central Board of Direct Taxes
North Block, New Delhi
26th August, 2015
To
All the Pr. CCsIT/ CCsIT/Pr. DGsIT/DGsIT

Subject: Guidelines for Grant of Reward to Informants leading to Recovery of Irrecoverable Taxes, 2015.

Madam/Sir,

In supercession of the guidelines for Grant of Reward to Informants, 2007, I am directed to say that the following guidelines will regulate the grant of reward to informants leading to recovery of taxes of tax defaulters whose names have been published in the public domain under section 287 of the Income-tax Act, 1961.

1. Short title
These guidelines may be called the 'Guidelines for Grant of Rewards to Informants leading to Recovery of Irrecoverable Taxes 2015'.

2. Application of Guidelines
(i) These guidelines will regulate the grant and payment of reward to informants who provide specific and credible information of the whereabouts/assets of persons, on or after 31.03.2015, which results in the collection of taxes, penalties, interest or other amounts (hereinafter “tax") already levied under the Income Tax Act, 1961 and the Wealth Tax Act, 1957. Grant and payment of reward for information provided before 31.03.2015 will continue to be regulated by 'Guidelines for Grant of Rewards to Informants 2007'.

(ii) These guidelines will be applicable if the jurisdictional Pr. Chief Commissioner/Chief Commissioner is satisfied that the tax could not be recovered despite all possible efforts having been made by the Department to trace the defaulter assessee or his assets and the information provided by the informant has resulted in recovery of tax.

3. Reward Amount
i. Individuals will be eligible for rewards based on the tax collected as a result of any administrative or judicial action resulting from the information provided. The quantum of tax collected will be determined only after all assessments have become final and no appeal/revision/other litigation is pending.

ii. The reward would not exceed 10% of the tax, recovery of which is directly attributable to the information/documents supplied by the informant, subject to a ceiling of Rs. 15 lakh. The full Board may relax the ceiling of Rs. 15 lacs on the basis of recommendation of the Committee mentioned in paragraph 4 below.

iii. Reward should be processed and granted in respect of recoveries directly attributable to the information furnished by the informant which was not in the knowledge of the department. Any proposal to this effect must be mooted after recoveries have been made of irrecoverable taxes and there is no further litigation. Reward will be only with reference to taxes recovered in the case of a taxpayer about whom information is given.

iv. Reward in accordance with these guidelines is discretionary and will be in the nature of ex-gratia payment which, subject to these guidelines, will be granted in the absolute discretion of the authority competent to grant rewards. No representation or petition against any decision regarding grant of rewards will be entertained from either the informant or any person on his behalf and the outcome of the claim cannot be disputed in Court.

v. The reward under these guidelines is in the nature of ex-gratia payments and accordingly, no assignment thereof made by the informants will be recognized. The authority competent to grant rewards may however; grant reward to heirs or nominees of an informant of an amount not exceeding the amount that would have been payable to the informant, had he not died.

4. Authorities Competent to Grant Reward
The authority competent to grant reward will be the Pr. Chief Commissioner of Income Tax/ Chief Commissioner of Income Tax in whose charge the arrears, from which recovery is made, are recorded .However, where the amount of reward in any given case exceeds Rs. 1,00,000/-, the same should be approved by a Committee of three officers comprising the Pr.Chief Commissioner (Pr.CCIT) of the region as Chief Commissioner concerned and one other Chief Commissioner of Income Tax nominated by the Pr. CCIT. In case Pr. Chief Commissioner of Income Tax is also the Chief Commissioner of Income tax concerned, he/she shall nominate one more Chief Commissioner as member of the committee.

5. Informants for the purpose of the guidelines:
i. A person will be considered to be an informant eligible for reward in accordance with these guidelines if he furnishes specific information in relation to assets/untraceable assessees concerning irrecoverable taxes. However, the claim of reward shall be confined to cases where action is actually taken in pursuance of the information. The information provided must be supported by facts/documents and should not be speculative, vague, of general nature or an "educated guess".

ii. In the cases where the documents or supporting evidence are known to the informant but are not in his possession, the informant should describe these documents and identify their location to the best of his ability.

iii. The information can be submitted to the j urisdicational Pr.CCIT/CCIT/Pr.CIT of the assessee as mentioned vide publication of his name in public domain or in the office of any other Pr.CCIT/CCIT to the officers designated as nodal officers( not below the rank of Addl/JtCIT) for receiving the information. Information received by any other Pr.CCIT/CCIT will be forwarded within 15 days to the jurisdictional Pr.CCIT/CCIT.

iv. The jurisdictional Pr.CIT will be act as the Nodal Authority to examine and decide the nature of actionable information as provided in Annexure-A.

6. Statement of informant
Where any information or evidence is furnished by any person in the expectation of a reward, he will be required to furnish a written statement as per Annexure - B to these guidelines. Such a statement should be signed by the informant in the presence of the Nodal Officer, to whom the information is furnished. Where any information is received by post intimating that the information is given with a view to claim reward, the informant should appear before and sign the written statement in the presence of such authority. The original statement in all cases should be kept in the custody of the jurisdictional Pr. CIT No reward shall be admissible if the informant refuses to give the written statement as referred to above.

7. Written undertaking of the informant
At the time an informant furnishes, in the expectation of a reward, any information or documents, an undertaking should be taken from him to the effect:

a) That he is aware that the information or documents furnished by him do not ipso facto confer on him the right to any reward and that he would be bound by the decision of the competent authority in this regard.

b) That he is aware that the extent of reward depends on the precision of the information and usefulness of the documents furnished by him;

c) That the reward would pertain only to as much of the taxes recovered/realized as are directly attributable to the information supplied by him;

d) That the provisions of section 182 of the Indian Penal Code have been read by him or explained to him and he is aware that if the information furnished by him is found to be false he would be liable to prosecution;

e) That he accepts that the Government is under no obligation to enter into any correspondence regarding the details of any taxes realized as a result of his information and;

f) That he accepts that payment of reward is ex-gratia in the absolute discretion of the authority competent to grant rewards and he has no right to dispute the correctness of the decision in any court of law.

8. Circumstances to be kept in view in determining the amount of reward In determining the reward amount, the authority competent to grant the reward will keep the following in view:

a) The accuracy of the information given by the informant.

b) The extent and nature of the help rendered by the informant.

c) The risk and trouble undertaken and the expense and odium incurred by the informant in securing and furnishing the information and documents.

d) The quantum of work involved in utilizing the information furnished and the facility with which such tax could be recovered as a result of the information.

6) The quantum of tax recovered which is directly attributable to the information and documents supplied by the informant.

f) The quantum of reward already given in terms of Guidelines for Grant of Rewards to Informants, 2007 where the proceeding or action for levy of tax was originally initiated on the basis of information provided by the same informant.

9. Secrecy of the identity of the informant 
The identity of the informant shall be kept secret if so desired by him by giving him a number. No information relating to informants or the rewards paid to them shall be disclosed to any authority except in accordance with any law for the time being in force.

10. Certificate from the Internal Audit Par
When the amount of reward is Rs] lakh or more, the Pr.CCIT or CCIT concerned shall, before the grant of reward, get the case checked and obtain a certificate from the concerned Pr.CIT regarding the correctness of the taxes recovered. 

11. Prohibition of rewarding in certain cases
No reward shall be granted if-

i. The informant is a Government servant who furnishes information or evidence obtained by him in the course of his normal duties as a Government Servant.

Explanation: A person employed as an employee by the Central or any State or any Union Territory Government or a nationalized bank or any local authority or any public sector undertaking, corporation, body, corporate or establishment, set up or owned by the Central Government or any State Government or any Union Territory Administration shall be deemed to be a Government Servant for the purposes of this paragraph; or 

ii. The informant is required by law to disclose the information to the Department; or

iii. The informant has access to the information on the basis of a contract with the Government

iv. The Scheme should be confined to only cases where;

(a) assessee is not traceable,

(b) there are no/inadequate assets for recovery,

(c) self assessment tax is outstanding for more than 6 months,

(d) TDS has been deducted but not deposited for more than 6 months.

and their names have been published in public domain under section 287 of the Income Tax Act, 1961.

12. Maintenance of record of each informant and not taking cognizance of Information furnished by certain informant.

The Authority competent to obtain information, evidence or documents from informant will maintain record of each informant, giving in brief his antecedents, the details of cases in which he has furnished information and the extent to which information has been found reliable. In case it is found that the antecedents of the informant, the nature of the information furnished by him in the past and his conduct justify ignoring the information, evidence and documents furnished by him, the case should be referred by such authority to the Pr. CCIT/CCIT concerned, and if approved by him, it would be open for such authority not to take cognizance of the information furnished by such an informant.

13. Drawing of the bill
The orders of the authority competent to grant reward in cases where such authority is himself competent to grant reward and in case in which the decision vests with the Committee referred to in paragraph 4 are applicable, sanction of the Committee referred to therein, will constitute sufficient authority for drawing the bill on the treasury against the sanctioned allocation.

14. Control and audit expenditure relating to rewards
The control and audit of the expenditure for reward will be governed by the instructions specifically issued for the purpose from time to time.

15. Hindi version shall follow.

(Sandeep Singh)
Under Secretary to the Government of India

Annexure-A
.
INTERNAL GUIDELINE TO DECIDE ACTIONABLE INFORMATION.

1. The information should be first hand information.

2. Information should be in written form clearly indicating:-

I. Movable assets from which recovery can be made.

i. Bank Name/ Branch.
ii. Account Number
iii. Amount shown as balance.
iv. Proof of share/debentures & any other negotiable instrument.

II. Immovable Assets.
a. Undisclosed property, shops investment in real estate.
b. Probable inheritance by court decree or any other incident.
c. Proof of purchase or sale or ownership of property.

III. Investment in foreign Bank Accounts, Assets.

3. Necessary conditions for treating the information as actionable:

a) The information must be backed by proof.

b) It should be in writing.

c) The information must be authentic and not mere suspicion, conjectures or imaginary concept

d) The evidence must be visible and direct, actionable and not directed at realisation after liquidation, partition of firm/company etc.

e) information must not be known to the department. If at a later stage it is found that information was not unknown, reward will be denied.

form+of+statement+of+furnishing+regarding+assessee+assets+for+recovery+of+outstanding+demand
Annexure-B Page1
declaration+by+informant
Annexure B Page-2


Source: http://irsofficersonline.gov.in/Documents/OfficalCommunique/193201533215.pdf

Read more: http://www.staffnews.in/2015/09/guidelines-for-grant-of-reward-to.html#ixzz3kpxTQgxF
Under Creative Commons License: Attribution Share Alike
Follow us: @karnmk on Twitter | cgenews on Facebook

Seventh Pay Commission Likely To See Pay Hikes By 40%

New Delhi: The Seventh Pay Commission’s report is likely be submitted to the Finance Minister Arun Jaitley shortly.

Sources said that there will be no internal relief. The average increase in basic fair pay for all government employees will be in the region of 40-45%.

This is a very rough average because for senior level officers, like the Cabinet Secretary or officials at the secretary level, the payback could increase by more than 50%.

The Pay Panel may recommend a new pay scale from January 1, 2016. The existing HRA would be retained for A1 cities; while there would be a 15-20% hike for other cities.

But interestingly when we spoke to government employees they were not really happy, essentially perhaps because effectively if you take the DA out, it is not really a hefty bonanza as many thought it would be.

Also, given the price rise and inflation issues, most people are saying it is really not that sufficient. Most of these recommendations will be implemented.

But the point is, if the Finance Minister Arun Jaitley has a problem with any of the observations or with the impact of the Pay Commission recommendations, he might even send this back to the Pay Commission for another round of iteration.

In that case, some amendments will be made that come back to the Finance Ministry and then it may go back to the Cabinet for approval. If that happens, it could delay the process by about 1-2 more months.

A central government employee will earn up to 40% more if the government accepts the Seventh Pay commission’s proposals, which will be submitted shortly.

This pay hike would affect the lives of over 48 lakh central government employees and 55 lakh pensioners and could trigger off similar pay hike across state governments as well.

An official of the Pay Commission, says recommendations will be made to improve productivity.

The Commission will be talked of market driven compensation at the top level, where there are expert persons required by the government. There should be open competition with the public. If they have a better candidate from outside, he should be appointed instead of simply promoting people by seniority, said the official.

Source:http://www.tkbsen.in/2015/09/seventh-pay-commission-likely-to-see-pay-hikes-by-40/