EXCEL EXPERTS: An IF Formula Challenge for you

Sunday, July 15, 2012

An IF Formula Challenge for you

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.
IF Excel Formula - Sytanx & Explanation

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.