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 requestC.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 500Overall 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.
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.