Formulaic Confusion Part 1: BEDMAS

Last Friday, an Experience Matters student who is currently working on Excel asked me about formulas. She said she just couldn't seem to get the hang of them, and above all, what did she need to remember for the Microsoft Office Specialist exam? Now this answer is going to come in three parts, partly to keep this clear, and partly to make up for the fact that it's almost the end of January, and I haven't posted once in 2008 yet!

The three things I want to address (and in this order!) are: BEDMAS, Operators and Absolute References. Lets start at the beginning, and this will make things incredibly straightforward for all your formulaic needs!

BEDMAS
This is a handy little acronym which I never learned in school, but it is used as a mathematical standard everywhere. Basically, each letter stands for a word to describe a type of calculation. The order of the words signifies the order you do your calculation. Each word is as follows:

Brackets ( )
Exponents m²
Division / (this is the sign to show division on the computer)
Multiplication * (again, this is on the computer)
Addition +
Subtraction -

An example of the kind of question you might be asked is as follows:

You are in charge of putting price tags on 10 products, all of which are to be marked up by the same percentage on cost. You are nearly finished with a worksheet that will do the necessary calculations. The final step involves entering a formula to compute the selling price of the first item and copying it to calculate selling price for other products.

When you open the file to work in, it looks like this:











(If your version of Excel looks a little different to mine, don't worry - I'm using Excel 2007)

The calculation will start in cell C6, and we need to find out two things. First, we need to find out what 25% of the cost is (for row 6, the cost is 8), and then we need to add that number to the original price of the item. I always find that writing the calculation out in "English" helps me visualize the steps I need to take in the calculation. One thing I WAS taught in high school is that if you are looking for 25% of something, the word "of" stands for "multiplied by". So here we say:

25%*8 (which equals 2)

We also want to add the original cost of the item to this, so that at the end we have the total price the item will be sold for. Therefore:

25%*8+8

OR:

D4*C6+C6

Because the BEDMAS rule applies, the multiplication is done before the addition. I'm the kind of paranoid person who definitely understood about brackets, and I would throw them in everywhere to make sure that one part of the calculation would be calculated first. But here we don't need to do anything like that. The computer first works out the 25% of 8, and then adds it to the 8.

Everything clear so far? Well you'll have to wait till tomorrow to find out step two! ;) Tomorrow we'll be talking about operators. See you then!