return excel basics 09 - 1 microsoft excel basics mathematical operators & hierarchymathematical...

Excel Basics 09 - 1Return

Microsoft Excel Basics

Mathematical Operators & Hierarchy - 2( ) Parenthesis pairs^ Exponentiation

* Multiply and / Divide+ Addition and – Subtraction Z-Statistic Calculation - 3 Square Root - 7 Standard Error Calculation - 7

Lesson 09

Mathematical Operators (More Complicated Formula)


Mathematical Operators

Creating formula to handle calculations is necessary to create effective business worksheets. Formula can take on many forms: Mathematical, Statistical, Logical, Financial, etc. Excel has many built in functions to handle many situations; however, the ability to build a formula with the proper syntax is important.

The key mathematical operators are ( ) Parenthesis pairs^ Exponentiation * Multiply and / Divide+ Addition and - Subtraction

Excel caries out mathematical calculations in the hierarchy shown above. A calculator is necessary when checking mathematical formula converted to Excel.


Converting … Mathematical to Excel

One of the first things we need to know is how to convert mathematical formula to an Excel formula. You will recognize this Z-Statistic formula.

Mathematical formulaZ =

X - M

SExcel formula are quite different since all of the equations have to be typed on one line. We have two options for the above formula:

Which one do you think is correct. Let’s look at some calculations.

Z = (X - M) / S

Z = X - M / SExcel Option 1:

Excel Option 2:



Because correct formula are critical, care should be taken every time you create one in Excel. The Golden Rule is to test the formula with some simple numbers to ensure your Excel formulation is correct. Let’s calculate Z when X=5, M=1 and S=2. First,

Mathematically

ZX - M

S

5 - 1

2

4

2= 2



Let’s calculate Z when X=5, M=1 and S=2. First,

Excel Option 1: Excel Option 2:

Z = X - M / S

= 5 - 1/ 2

= 5 - .5

= 4.5

Z = (X - M) / S

= (5 - 1) / 2

= (4) / 2

= 2The Excel calculation hierarchy is why Option 2 is correct!

( ) Parenthesis pairs^ Exponentiation* Multiply and / Divide+ Addition and - Subtraction


Create this formula and save it as F02-Calculate Z Statistic

Note: The Formula Toolbar for cell A2



Let’s look at a little more complicated formula using the Excel square root function. The formula calculates the Standard Error for large samples from two populations.

Mathematical formula

S =S

n

S

n12

1

22

2

Excel formula

S = SQRT(S ^2 / n S ^2 / n )1 1 2 2


Applying the Golden Rule (test the formula with some simple numbers), let’s calculate S when S1=3, N1=30, S2=5, and N2=50.

Mathematically


S =S

n

S

n

3

30

5

50

9

30

25

50

= .3+.5

12

1

22

2

2 2

. .8 89442719



Let’s calculate S when S1=3, N1=30, S2=5, and N2=50.

Excel

S = SQRT(S ^2 / n S ^2 / n )

= SQRT(3^2 / 30 +5^2 / 50)

= SQRT(9 / 30 + 25 / 50)

= SQRT(.3+.5)

= SQRT(.8)

=.89442719

1 1 2 2


Create this formula, set the decimal place accuracy for cell A2 to 4 and and save it as F03-Calculate Standard Error 2 Populations

Note: The Formula Toolbar for cell A2

return excel basics 09 - 1 microsoft excel basics mathematical operators & hierarchymathematical...

Documents