Auto Sum Formula Exmple :-
You can use the AVERAGE function to find the meanaverage cost of all entertainment for January and February.
The preceding example demonstrates how you can nest IF statements. In each formula, the fourth IF statement is also the value_if_false argument to the third IF statement. Similarly, the third IF statement is the value_if_falseargument to the second IF statement, and the second IF statement is the value_if_false argument to the first IFstatement. For example, if the first logical_test argument (Average>89) evaluates to TRUE, "A" is returned. If the first logical_test argument evaluates to FALSE, the second IF statement is evaluated, and so on. You can also use other functions as arguments.
The letter grades are assigned to numbers, using the following key.
-Sum
-Average
-If
-Hyperlink
-Count
-Max
-Min
-Sin
-Sum If
-Pmt
-Average
-If
-Hyperlink
-Count
-Max
-Min
-Sin
-Sum If
-Pmt
SUM
The SUM
function provides a quick way to sum columns or rows of
numbers in an Excel worksheet.
EX:-
4+4+87+20=115 is a Sum of 4,4,87,20
Other Exmple:-
-Shortcut use for this formula
Alt+(+) after click enter
Sum Formula basically हम कोई दो या दो से जादा value के add करने के लिए use करते है । sum निकालने का short cut भी formula use कर सकते है। example :- Alt और + और enter एक साथ press करके भी हो सकते है।
exmple :- 1 2 3 4 5 = (Alt)( +)(enter)- 15
AVERAGE
Excel will enter the formula for you. Click in cell D7. On the Home tab, in theEditing group, click the arrow on the Sum button, and click Average in the list.
The formula =AVERAGE(B7:C7) appears in the formula bar near the top of the worksheet.
You could also type the formula directly into the cell.
Note The Sum button is also located on the Formulas tab, in the Function Library group.
IF
IF function
This article describes the formula syntax and usage of the IF function in Microsoft Office Excel.
Description
The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.
Syntax
IF(logical_test, value_if_true, [value_if_false])
The IF function syntax has the following arguments:
- logical_test Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
- value_if_true Required. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and thevalue_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IFfunction returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.
- value_if_false Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and thevalue_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IFfunction returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_falseargument is blank (that is, there is only a comma following the value_if_true argument), the IF function returns the value 0 (zero).
Example 1
A | B | C |
---|---|---|
Data | ||
50 | 23 | |
Formula | Description | Result |
=IF(A2<=100,"Within budget","Over budget") | If the number in cell A2 is less than or equal to 100, the formula returns "Within budget." Otherwise, the function displays "Over budget." | Within budget |
=IF(A2=100,A2+B2,"") | If the number in cell A2 is equal to 100, A2 + B2 is calculated and returned. Otherwise, empty text ("") is returned. | Empty text ("") |
Example 2
A | B | C |
---|---|---|
Score | ||
45 | ||
90 | ||
78 | ||
Formula | Description | Result |
=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) | Assigns a letter grade to the score in cell A2 | F |
=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) | Assigns a letter grade to the score in cell A3 | A |
=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) | Assigns a letter grade to the score in cell A4 | C |
The preceding example demonstrates how you can nest IF statements. In each formula, the fourth IF statement is also the value_if_false argument to the third IF statement. Similarly, the third IF statement is the value_if_falseargument to the second IF statement, and the second IF statement is the value_if_false argument to the first IFstatement. For example, if the first logical_test argument (Average>89) evaluates to TRUE, "A" is returned. If the first logical_test argument evaluates to FALSE, the second IF statement is evaluated, and so on. You can also use other functions as arguments.
The letter grades are assigned to numbers, using the following key.
IF SCORE IS | THEN RETURN |
---|---|
Greater than 89 | A |
From 80 to 89 | B |
From 70 to 79 | C |
From 60 to 69 | D |
Less than 60 | F |
Example 4
In this example, the LOOKUP function is used instead of the IF function because there are thirteen conditions to test. You may find the LOOKUP function easier to read and maintain than the IF function.
A | B | C |
---|---|---|
Score | ||
45 | ||
90 | ||
78 | ||
Formula | Description | Result |
=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) | Assigns a letter grade to the score in cell A2 | F |
=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) | Assigns a letter grade to the score in cell A3 | A- |
=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) | Assigns a letter grade to the score in cell A4 | C+ |