Excel Class Free VBA Tutorial – Variables

Few things in life are constant. Heraclitus said: The only thing constant is change. Change is constant and variables are used to capture change. In the context of Excel, the names of workbooks change, the number of workbooks you have open is variable, the number of sheets, pivot tables and charts is usually variable, the name of sheets is variable, the values in cells are variable, and on, and on.

Continue reading

PEMDAS and the Mathematical Order of Operations with Microsoft Excel

Microsoft Excel works similarly to using a calculator but there are important differences. On a calculator to perform a calculation you might type numbers, then math symbols like plus (+) or minus (-) and then you would press the equal key to complete your calculation.In Excel we don’t end with the equal sign, we begin with the equal sign. Additionally, you need to be aware of the mathematical order of operations, commonly associated with the acronym PEMDAS (Please Excuse My Dear Aunt Sally).

What the order of operations theorem states is that parenthesis () overrides exponentiation (^) which has a higher precedence than multiplication (*) or division (/) and that multiplication and division have a higher order of precedence than addition (+) and subtraction (-). For those of us who don’t have degrees in rocket science, exponentiation is the term used to say raise one number to the power of another number, like 28 which would equal 256 (2*2 eight times).

To see how PEMDAS applies to real world examples let’s look at two formulas:

Example of PEMDAS:

= 2 + 3 * 4

Most people would read that formula from left to right and calculate 2+3 equals 5 and 5 times 4 equals 20, and it wouldn’t be entirely wrong. However, because multiplication has a higher order of precedence than addition, Excel will first calculate 3*4 equals 12 and 12+2 equals 14.

Therefore, if you want the formula to produce 20 instead of 14 you need to override the order of operations by grouping the 2+3 terms inside parentheses as in:

= (2 + 3) * 4

In Excel formulas it is common to see many many parentheses. That is because most functions (predefined formulas) use parentheses to enclose the arguments (inputs) and those arguments may nest other functions in parentheses and each function may require parenthesis around the inputs. For example,

=IF(D2=”R”,INDEX($K$3:$K$8,MATCH(B2,$J$3:$J$8,FALSE)*C2),IF(D2=”C”,INDEX($H$3:$H$8,MATCH(B2,$G$3:$G$8,FALSE)*C2),IF(D2=”H”,INDEX($N$3:$N$8,MATCH(B2,$M$3:$M$8,FALSE)*C2))))

What is important to realize about using parentheses is that if you use () symbols you have to be careful to balance every open parentheses with a close parenthesis or the formula will produce an error. If you’re unsure of why this formula uses $ signs in the cell references, please refer back to our blog about absolute versus relative cell references.

To learn more about our Excel Training in New York City contact us here.