As we discussed yesterday, a formula in Excel must begin with “=”. Presence of “=” at the beginning of a cell, indicates to Excel that some work has to be done on the contents succeeding “=”.
“=” can also be used to input mathematical expressions for calculations by Excel. For example, we could have used the mathematical expression =23+48+68 in place of the formula used yesterday =SUM(23,48,68) to get the same result. We can also use mathematical expressions together with a formula to get the results. For example, =5*AVERAGE(43,25,75,85) will multiply the average of 43,25,75 and 85 with 5 and display the result in place of the mathematical expression entered. Off course, you can get the same result by using the mathematical expression =5*((43+25+75+85)/4).
Till now, we have used absolute values, such as 23, 43, 75 etc., in all the inputs we have made so far. If Excel was limited to this, it will not be much better than a calculator. Where Excel excels is its ability to not only accept absolute values but also reference to their location. But that is a topic for some other day.
Today, please use all the mathematical expressions and formula discussed so far, in your copy of Excel and a few variations of them to get a good idea of their usage. As you use the SUM or AVERAGE formula, notice that:
i) As you start typing the formula after inputting “=”, Excel presents you with a list of possible formula beginning with the letter. As you keep typing, Excel keeps refining the list of possible formula. See the illustration below:
ii) Excel provides a brief detail of the formula. See the description of the SEARCH formula in the above illustration and description of the SUM formula in the illustration below. Notice also that as SUM has been entered, Excel has refined the list to all the formula beginning with Sum.
iii) An Excel formula has to be supplied with a set of inputs on which it does the necessary calculations. These inputs are called as Arguments. For example, in the formula =SUM(23,48,68), the numbers (23,48 and 68) are arguments. As you would have noticed by now, the arguments to a formula are enclosed within parentheses “()” e.g. =SUM(23,48,68) or =AVERAGE(43,25,75,85)
iv) Once you have entered a formula name and put opening parentheses, Excel also tells you the kind of arguments that it expects you to enter within the parentheses. See the illustration below:
Please use all mathematical expressions and formula discussed so far, to have a clearer understanding.
Please email me if anything above is not clear. Please also send your feedback and requests for the topics that you wish me to cover. I promise to do my best at fulfilling your expectations.