The real strength of Excel is its calculation power. An excel worksheet without any calculation can also be created in an MS Word (.doc or .docx) file. This post will help to understand excel formulas, from simple calculations to advanced ones, with nested built-in worksheet functions.
Basics:
A formula always starts with an equal (=) sign. If you don’t type equal (=) sign, excel will treat the same as text only.
When you enter a formula in cell, it also appears in formula bar, but once you press enter or arrow key to lock the formula, though the cell contains formula, answer is displayed on screen.
Some of the mathematical operators used in excel are given here-under
^ Exponentiation (to the power)
* / Multiplication and Division
+ – Addition and Subtraction
& Text Joining
= < > <= => <> Comparison
Above operators are shown in descending order of priority, if a formula contain operators of same priority excel calculate it from left to right. To change the order of evaluation you have to use (brackets). Any part of formula in (brackets) is done first. You can see how (brackets) affects the answer in following example:-
Formula Answer
=5*2+12/3-1 13
=5*2+12/(3-1) 16
=5*(2+12)/(3-1) 35
=(5*2+12)/(3-1) 11
=5*(2+12/3)-1 29
To practice creating a formula, try this:
- Think of an integer (whole) number
- Multiply the number you thought of by 2
- Add 4 to the result
- Divide the result by 2
- Subtract the number you thought of
Check your answer it should be 2, if it’s not check your formula and adjust (brackets) wherever necessary.
Worksheet Functions:
Excel have nearly 500 built-in worksheet functions for standard calculations and the number is adding on with release of new versions. You may access the complete list of worksheet functions at link given below, you will also get information about version to which a worksheet function was introduced. A function will not work in earlier versions to which it was introduced.
You may use a single function or nesting more than one as required.
If required you may develop your own worksheet function but this topic is beyond the scope of this article.
Array Formula:
As I stated at the beginning of this post that the real power of excel is its calculation power, array formulas manifold it. With array formula you may enter different values in multiple cells in one go, can do multiple calculations and can pick multiple values with array formula.
Always remember you have to enter an array formula by pressing CTRL+SHIFT+ENTER together. When you enter an array formula as stated above excel cover the whole formula with curly braces as shown below.
{={0,1,2,3,4,5,6}+1}
Just select 7 contiguous cells in a row and enter above formula excel will insert value of 1 to 7 in seven cells. To enter the data in cells of 1 column replace (,) by (;). An array may reside in single cell or multiple cells of a worksheet.
Suppose, we have to add numbers from 1 to 10, we can do this with worksheet function “SUM” as shown below:
=SUM(1,2,3,4,5,6,7,8,9,10)
You can also achieve same result with below array formula
=SUM(ROW(1:10))
I have not shown start and end with curly braces in the above formula as you don’t have to put the same, excel will do it for you when you press CTRL+SHIFT+ENTER together.
You can feel the power of array formula with above example, here I choose to add 1 to 10 only, just think what if we have to add from 1 to 100 or 1 to 500.
Dynamic Array Functions:
In 2020 Microsoft introduced a new feature called Dynamic Array Functions. Now, you are not required to press Ctrl+Shift+Enter for array formulas. If your array formula will return multiple results, it will automatically spill over to a range of cells and there is no need to select multiple cells explicitly for entering the formula. However, this feature is/will available only in office 365 based on the version you are using. For more details, please visit the links below.
Cracking the Formulas:
Sometimes you will find that your formula is not working as expected. For this you have to understand how excel is evaluating your formula. For this, a number of tools are available. “Evaluate Formula” is most handy to understand formula applied. It’s available under “FORMULAS” tab in “Formula Auditing” group. Let us evaluate below formula (after updating value of 1 to 10 in excel worksheet range “A1:A10”) as shown below:
=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
With the help of above formula we are counting unique values in range “A1:A10”. Here, we have used three worksheet functions “SUM”, “IF” and “FREQUENCY”. If you are not aware of these functions first of all check details by pressing F1 in any excel workbook then entering name of function in search box. You can also check these functions at the link give above in “Worksheet Functions”.
Now, select the cell containing above formula and go to FORMULAS > Formula Auditing > Evaluate Formula and click “Evaluate”. As we know excel calculate from left to right and inside out you will get the following results by continue pressing “Evaluate” button till to reach answer.
=SUM(IF({1;1;1;1;1;1;1;1;1;1;0}>0,1)) by calculating “FREQUENCY(A1:A10,A1:A10)”
=SUM(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE},1)) by calculating “FREQUENCY(A1:A10,A1:A10)>0”
=SUM({1;1;1;1;1;1;1;1;1;1;FALSE}) by calculating “IF(FREQUENCY(A1:A10,A1:A10)>0,1)”
And reach to 10 by calculating whole formula, means all values are unique.
CAUTION: Please do remember that this formula will work only for numbers not for text.
Look inside formula by HAWK EYE-“F9”:
You can check how excel evaluating any complete portion of your formula by selecting the same and pressing F9. In case portion selected by you is not complete excel will give error message.
CAUTION: Please do remember to press Escape/ undo button after checking how excel evaluating your formula by pressing F9, else excel will replace the formula with calculated value.
Help us in our endeavor to spread the knowledge of excel!
Join us and share your thoughts with us in the comments section below.
0 Comments