How To Use 'IF' Function In Spread Sheet

‘IF’ worksheet function is used to check the logical result of a condition and based on that result, directing Excel to be perform an action. Actions include doing some calculation or returning to a value.

The logical result of a condition is either true or false. If the condition is true, the function performs an action. In case if the condition is false, the function performs another action.

The syntax is

=IF(CONDITION, if true what to be executed, if false what to be executed)

Go through some of these examples

=IF(E6>100,E6,””)

Here E6 is the cell, which has some value that may be pure number or result of any formula. If value of E6 is greater than 100 return E6 else return nothing (NULL)

=IF(E6>100,E6,0)

If value of E6 is greater than 100 return E6 else return 0

=IF(E6>100,(E6*5),E6)

If value of E6 is greater than 100 return multiplying E6 with 5, else return 0

=IF(E6>100,(E6*5),(E6*6))

If value of E6 is greater than 100 returns multiplying it with 5, else multiply with 6

=IF((E6+F6)>100,1,0)

If sum of E6 and F6 is greater than 100 return as 1 else 0

Important: Do not leave space within formula. Use commas ( , ) Parentheses correctly.

I hope the above examples helped you to get an idea about 'IF' function. Now, go ahead and try yourself to make some formulas using 'IF'.

Tips: You can even use more than one (up to 8) ‘IF’ function in a single formula.

=IF(E6=1,100,IF(E6=2,200,IF(E6=3,300,IF(E6=4,400,IF(E6=5,500,

IF(E6=6,600,IF(E6=7,700,IF(E6=8,800,0))))))))

Learn How To Summarize Your Data Instantly

If you want to summarize your data fast use Subtotal command to calculate automatically each similar groups and their grand total.

Info: Ensure yourself that your data is labeled and sorted properly before using Subtotal command.

You can find the subtotal command in the Data menu. Excel opens a dialogue box once you click it and displays all the labels in the drop menu ‘At each change in’ in the top of the dialogue box.

Now you need to select one label from the list. The label you selected becomes the Index and other labels are considered as items.

Indexing is done to create a tracking order for calculating subtotal of other items. If there is a change in data in the index Microsoft Excel stops summarizing the values till that point, makes a total and continues the summarizing next similar data.

You can use any one of the FUNCTIONS (COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR & VARP) in the dialogue box to find value of items. You can find this option in the middle of the dialogue box.

In the bottom of dialog box all the labels are displayed in the scroll menu ‘Add subtotal to’ and Excel ask you to select one or more than one item that you want to summarize. Selected items are summarized by the FUNCTION based on Indexed label.

The other features in this dialogue box are:

Replace Current Subtotals: In case you summarized a particular field using Subtotals and want to change the index label or summarizing function or items, open subtotal dialogue box make necessary changes in inputs and enable ‘Replace Current Subtotals‘ option. Excel will remove existing subtotal and replaces with the new one based on your input.

Page break between Groups: If data taken to summarize is huge and it is required to take print out for each item, you need to enable the option ‘Page break between Groups’. Excel prepares page break between each group.

Summary below data: This option helps you to get summary details below each summarized item.

Tips: If you want to remove subtotals and get back original data, open subtotal dialogue box and select ‘Remove All’.

The result will have outlines that helps you to overview the data. Outlines help you to expand or collapse the nested items without affecting the data. You can turn off outlines if not required. Open options dialogue box and disable ‘outline symbols’ from windows options in the view tab.

Tips: Press Ctrl key and numerical key 8 at the same time to turn ON or OFF outlines.