An IF Formula Challenge for you

If I were to hire an data analyst, I would simply ask them to write a complex IF formula in Excel. If they can write it, the interview progresses, else, they are out. In other words,

=IF(person_can_write_big_fat_IF_formula=TRUE, proceed_with_interview, say_thanks_and_call_next_person)

If you are able to write IF formulas for any situation, then you are bound to be awesome in Excel.

So, to test how well you know your IFs & Boolean functions, let me give you a small challenge.

Galo, one of our readers emailed this to me.

I would like to ask for your help in creating a tracker sheet for rewards program if they meet the qualifiers in order to avail it.
The conditions will be the following:

A.If the attendance is equals to 0% then they will earn Php 1500
If the attendance is less than 3% then they will earn Php 1000 only.

B.If the handling time less than 500 seconds reward will be Php 1000 (applicable only phone call request)
If the handling time less than 560 seconds reward will be Php 1000 (applicable only fax request)
Note: They can never have both phone and fax request

C.If there’s a commendation (1 or more) reward will be Php 1000

D.If the quality audit is 98% -100% reward will be Php 1500
If the quality audit is 96% -97.99 % reward will be Php 500

Overall if they were able to meet all of the qualifiers they can earn Php 5000.

Download the sample data

Click here to download the sample data. Your objective is to fill column I with appropriate IF formula to calculate the Cash bonus component.

Post your answers

Once you complete the formula, share them with me & others using comments. Go ahead and tell me the answer.
Note: when posting your answers, make sure you add space after any < or > symbols. Or else, they will not show up. So instead of writing A1>10, write A1 > 10 or A1 GT 10. Sorry for the extra hassle.

Learn how to use IF formulas

IF formula is an important for any Excel analyst. Read these articles to learn more about IF formula & related formulas.

• IF formula – what is it and how to use it?
• IF formula – 5 examples
• Introduction to SUMIF & COUNTIF formulas
• Introduction to SUMIFS formula
• Logical & Conditional formulas in Excel
• More IF Formula examples & scenarios

Thanks to Galo for emailing this question.