The Reader

From: CA Sudhir K Jaju, Aurangabad <skjajuco@gmail.com>
Subject: [aurangabad_ca] Today's Excel Update
To: Undisclosed-Recipient@yahoo.com
Date: Wednesday, 3 February, 2010, 12:02 PM

 

 

5 Basic Excel Short Cut Keys Chipake

 

KEY	FUNCTION
F1	FOR HELP
F2	EDIT THE ACTIVE CELL
F3	PASTE A DEFINED NAME INTO A FORMULA
F4	REPEAT THE PREVIOUS ACTION  (LIKE ADD ROW, COLUMN, ADJUST COLUMN WIDTH, COLOR FILL, ETC)
F5	DISPLAY  THE  GO TO  DIALOG  BOX

 

 

 

 

IF command

 

	Name	Sales	Target	Result
	Alan	1000	5000	Not Achieved	=IF(C4>=D4,"Achieved" ,"Not Achieved")
	Bob	6000	5000	Achieved	=IF(C5>=D5,"Achieved" ,"Not Achieved")
	Carol	2000	4000	Not Achieved	=IF(C6>=D6,"Achieved" ,"Not Achieved")
	What Does It Do?
	This function tests a condition.
	If the condition is met it is considered to be TRUE.
	If the condition is not met it is considered as FALSE.
	Depending upon the result, one of two actions will be carried out.
	Syntax
	=IF(Condition, ActionIfTrue, ActionIfFalse)
	The Condition is usually a test of two cells, such as A1=A2.
	The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.
	Formatting
	No special formatting is required.
	Example 1
	The following table shows the Sales figures and Targets for sales reps.
	Each has their own target which they must reach.
	The =IF() function is used to compare the Sales with the Target.
	If the Sales are greater than or equal to the Target the result of Achieved is shown.
	If the Sales do not reach the target the result of Not Achieved is shown.
	Note that the text used in the =IF() function needs to be placed in double quotes "Achieved".
	Name	Sales	Target	Result
	Alan	1000	5000	Not Achieved	=IF(C31>=D31,"Achieved" ,"Not Achieved")
	Bob	6000	5000	Achieved	=IF(C32>=D32,"Achieved" ,"Not Achieved")
	Carol	2000	4000	Not Achieved	=IF(C33>=D33,"Achieved" ,"Not Achieved")
	Example 2
	The following table is similar to that in Example 1.
	This time the Commission to be paid to the sales rep is calculated.
	If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.
	If the Sales do not reach Target, the Commission is only 5% of Sales.
	Name	Sales	Target	Commission
	Alan	1000	5000	50	=IF(C43>=D43,C43*10% ,C43*5%)
	Bob	6000	5000	600	=IF(C44>=D44,C44*10% ,C44*5%)
	Carol	2000	4000	100	=IF(C45>=D45,C45*10% ,C45*5%)
	Example 3
	This example uses the =AND() within the =IF() function.
	A builders merchant gives 10% discount on certain product lines.
	The discount is only given on products which are on Special Offer, when the Order Value
	is £1000 or above.
	The =AND() function is used with the =IF() to check that the product is on offer and that
	the value of the order is above £1000.
		Special	Order
	Product	Offer	Value	Discount	Total
	Wood	Yes	£   2,000	£          200	£         1,800
	Glass	No	£   2,000	£            -	£         2,000
	Cement	Yes	£     500	£            -	£            500
	Turf	Yes	£   3,000	£          300	£         2,700
				=IF(AND(C61= "Yes",D61>=1000),D61*10% ,0)

 

 

 

With Regards

 

CA Sudhir K Jaju
Aurangabad
9881150459