NESTED IFS, IF(AND), IF(OR)

NESTED IFS, IF(AND), IF(OR)

MODELLING : CONTENTS

? Nest ed IF f ormulae ? IF(Or.... . ) f ormulae ? IF(And...) f ormulae

Wherever you see t his symbol, make sure you remember t o save your work! ? teach- All Rights Reserved

NESTED IF, IF AND, IF OR

A simple IF...Then st at ement is where t here is a TRUE or FALSE condit ion. These are t he ones t hat you are more likely t o come across so it is import ant t hat you know how t o writ e t hese. However, some sit uat ions might require you t o be able t o writ e a Nest ed IF st at ement . They may look long and complicat ed, but t hey are really quit e simple once you underst and t he concept . A nest ed if st at ement will look somet hing like t his: =IF("if this condition stated here is true", then enter "this value, else if("if this condition stated here is true", then enter "this value, else enter"this value")) Let ' s break t his down int o simple st ages. You may have a spreadsheet t hat you use t o keep t rack of t he rat e of commission t hat your sales t eam earns. You may not want t o pay a f lat rat e of commission, perhaps it is f airer t o pay more commission t o t hose sales people who have worked harder and sold more it ems. You decide t o pay commission as f ollows: Sales f rom ?1 t o ?10 earns 10%commission Sales bet ween ?11 t o ?100 earns 15%commission If t hey sell over ?100 t hey earn 20%commission

TASK 1 Set up a spreadsheet wit h t he dat a on t he right We need t o writ e our nest ed IF so t hat we can f ind out t he correct commission t hey should earn. This is what t he nest ed IF f unct ion would look like: =IF(B2=t o 50 (but below 75), display ` Pass'

Let ' s do t he last part : " Fail" )) So, if t he mark isn' t above 50 or above 75, display ` Fail' . Remember t o put a double set of closing bracket s.

? teach- All Rights Reserved

Your f ormula should look like t his: =IF(B2>=75, "Distinction", IF(B2>=50, "Pass", "Fail"))

Have a go yourself . Not working? If somet hing goes wrong, it is usually because you have missed a comma, speech marks or a bracket .

Not ice, we did not need t o use any absolut e cell ref erences in t his f ormula as we were t yping in t he condit ions ourselves and not ref erencing anot her cell on t he spreadsheet .

TASK 3 ? Your t urn Open a new worksheet Type in t he dat a as shown on t he right

Inf ormat ion: ? If t he home result is great er t han t he away result , we want t o display ` Home' ? If t he home result is t he same as t he away result , we want t o display ` Draw' ? If t he away result is higher t han t he home result we want t o display ` Away' . Remember ? any words e. g. home, away or draw must be enclosed in speech marks f or Excel t o accept t hem.

In cell E1, have a go at writ ing your nest ed IF st at ement ? here is a lit t le hint t o st art you of f : =IF(B2>D2, " Home" ,

When you have writ t en your f ormula, drag it down t o cells E3 and E4. Check t hat you got t he right result s.

? teach- All Rights Reserved

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download