Intelligent Use Of Excel Formulas

By Author: Ed Bolton
Total Articles: 17
Comment this article

If you have ever used Microsoft Excel, chances are that you've tried to use Excel formulas. The great benefit of entering numbers into a spreadsheet, as opposed to a Word table, is that you can perform calculations with them.



The simplest Excel functions are addition, subtraction, multiplication and division. You can create a sum by typing "=", clicking on the first number you wish to add, typing "+", clicking on the second number you wish to add, and hitting enter. So far this should be familiar.



Very soon you reach a stage whereby you wish to sum a whole series of numbers. At that point, you should stop referring to each cell individually. Instead you would use the SUM function. It is the first function many people use and is written in the form "=SUM()".



However, how would you find the SUM function if you did not know the nomenclature? The answer is that you would use Excel help for formulas. To do this, you should type "=" in a cell, and then click on the "fx" button to the left of the formula bar. ...
... Excel then asks you to insert a function but lets you search for it. If you search for "add", Excel gives you the choice of two functions, "DSUM" and "SUM". If you click on "SUM", it provides the description "adds all the numbers in a range of cells". You don't need to be an Excel expert to find the function you want.



Once you have located it, you can also train yourself to use the SUM function. After selecting "SUM" from the list of available functions and clicking "OK", Excel will tell you how to define your addition.



The SUM function is very easy to use because you only need to select the cells you wish to add. Other Excel functions are more sophisticated e.g. WORKDAY. WORKDAY will calculate the end date for a task if given the start date, and the number of days the task will take. In this case, the start date and number of days may be in specific cells but you can also define a list of public holidays which should not be considered working days.



In theory, following the above process will enable you to find and learn the function. However, Excel requests an "optional array of one or more serial date numbers to exclude from the working calendar, such as state and federal holidays and floating holidays". This description, although accurate, is less user friendly than simply asking you to select the cells you wish to add.



So you may struggle to interpret Excel's guidance. The best way to resolve these problems is to do a web search for "Excel Workday function". This will give you an example with screenshots. By copying what you see online, you will be able to get an output from the function, which you can slowly adapt to give you what you need. If you get really stuck, you can always enlist the help of a consultant or request specialist training.



Hopefully this article will have given you a good idea how best to learn new formulas. To summarize; try to understand Excel's help and, if that doesn't work, copy and adapt an online example.



About the Author: Ed Bolton is the founder of Excel4Business, providers of Excel Help.