We are currently delivering Microsoft Excel Training Courses in Berkshire including Basildon, Bracknell, Reading, Slough, Wickham & Wokingham. We are specialists in on-site training and qualified to deliver Excel at Foundation, Intermediate & Advanced levels. For an overview of our Excel course content please visit the training courses page at our website. For more information email info@pbatraining.co.uk or call us free on 0800 2922842.
Month: May 2010
Constructing Simple Formulas in Microsoft Excel
Formulas in Excel perform calculations such as addition, subtraction and multiplication. They use the familiar arithmetic operators (+, – etc) however, formulas in Excel always begin with an equals sign (=).
Arithmetic Operators in Excel
+ Addition
– Subtraction
* Multiplication
/ Division
% Percent
Formula Construction
An example of a formula in Excel might be =2+2, which of course will return the answer 4. However, this would be a very restricted formula as it only returns one possible value. In practice, when we input formulas into an Excel spreadsheet we substitute raw data for cell references.
So an example formula might be =A1+B1. If the values in A1 and B1 are both 2 then the formula will return the answer 4 as before. However, we can now change the values in cells A1 and B1 and get different results without having to edit the formula.
As a general rule, we should try never to insert raw data into Excel formulas; it is almost always better to reference cells instead.
Here are some examples of Excel formulas:
- Subtract B1 from A1: =A1-B1
- Multiply A1 and B1: =A1*B1
- Divide A1 and B1: = A1/B1
Editing Formulas
Formulas can be edited in the cell by double-clicking with the mouse. They can also be edited in the formula bar by selecting the cell, then clicking inside the formula bar.
Combining Arithmetic Operators
If we wish to combine operators in the same formula it is sometimes necessary to separate them with brackets. this is especially true if we mix multiple & divide with addition and subtraction. This is because the operators of multiply and divide take precedence. Consider the following example:
We wish to add A1 and B1 then multiply the result by 20%. If we enter =A1+B1*20% we get the result 2.4. But this is incorrect, we should get 0.8.
The reason we don’t is that because multiplication takes precedence over addition, Excel will first multiply B1 by 20% and then add A1 to the product. In order to add A1+B1 first we need to wrap them in brackets, so our formula becomes =(A1+B1)*20%. This gives the correct result of 0.8.
Self-Test Exercise
- First enter the data shown into cells A1 and B1.
- Next add, subtract, multiply & divide the data in separate cells in column C.
- Finally, in C5 add the data and calculate 10% of the product.
Attending a Microsoft Excel Training Course
Our 1 day Foundation Microsoft Excel course covers formulas and many other essential skills. It is suitable for people with no previous experience of using Excel and also those self-taught users who would benefit from a structured training course to ‘fill in the gaps’ and extend their working knowledge of the application. We run onsite Microsoft Excel training courses throughout the UK, please call free on 0800 2922842 to discuss your IT training needs. Alternatively you can email us on info@pbatraining.co.uk.