An IF Formula Challenge for you

Posted on April 25th, 2012 in Excel Challenges , Excel Howtos - 136 comments

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.

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.

Thanks to Galo for emailing this question.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

136 Responses to “An IF Formula Challenge for you”

  1. Jamie Bull says:

    I named the ranges and split the formula over four lines for clarity:

    =IF(Attendance=0.96,IF(Quality>=0.98,1500,500))
    +IF(OR(AND(HT_voice”"),AND(HT_fax”")),1000)
    +IF(Commendation>=1,1000)

    • Cyril Z. says:

      Hi Jamie,

      I think you miss the 5000 overall condition at the end if all A, B,C and D conditions are fulfilled.

      Regards.

      • Jamie Bull says:

        I don’t think so. I interpreted that as saying the maximum available is 5,000 made up of 1,500 each for attendance and audit quality and 1,000 each for handling time and commendation.

        • Jamie Bull says:

          Although I’m also having a problem with a missing part where it’s being parsed as html.

          • Jamie Bull says:

            Ok, here’s what I had:

            =IF(Attendance<=0.03,IF(Attendance=0,1500,1000))
            +IF(Quality>=0.96,IF(Quality>=0.98,1500,500))
            +IF(OR(AND(HT_voice<500,HT_voice<>”"),AND(HT_fax<560,HT_fax<>”")),1000)
            +IF(Commendation>=1,1000)

            Thanks for the tip, Cyril Z.

    • Dinesh says:

      Dear All,
      The correct formula is :
       
      =IF(C4 = 0%, 1500, IF(C4 < 3 %, 1000, 0))+
       
      IF(OR(E4 < 500,F4 < 560), 1000,0)+
       
      IF(G4 > = 1, 1000,0 )+
       
      IF(D4 > = 98%, 1500, IF (OR ( D4 > = 96%, D4 <98), 500, 0))
      Thanks and regards
      Dinesh
       

      • Dinesh says:

        Dear All,
         
        Small Correction in my previous answer use AND instead of OR as follows:
         
        =IF(C4=0%,1500,IF(C4 < 3%,1000,0))
        +IF(OR (E4 < 500,F4 < 560), 1000, 0)
        +IF(G4 > = 1,1000,0)
        +IF(D4 > =98%,1500, IF(AND (D4 > = 96%, D4 < 98),500,0))
         

      • Dinesh says:

        In chandoo Solution the condition taken for quality is only >96% actually it should be Quality>=96%
         
        Thanks
        Dinesh

      • vaibhav says:

        the empty cells in column G of the table  are being considered to be greater than 1. Hence the condition for commendation is not being satisfied correctly.

  2. Davor says:

    here is my solution … maybe not the shortest one :D

    =IF(C4=0;1500;IF(C4=1;1000;0)+IF(D4>=98%;1500;IF(D4

  3. Wilson says:

    Love the use of (C4 in your solution

    To my shame I never realised TRUE*[number] and FALSE*[number] could be done in Excel formulas…

  4. Wilson says:

    Love the use of (C4

  5. Cyril Z. says:

    My IF solution,

    `
    =IF((IF(C4=0,1500,IF(C4″”,F4″”,G4>=1),1000,0)+IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))=5000,10000,IF(C4=0,1500,IF(C4″”,F4″”,G4>=1),1000,0)+IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))
    `

    Note that I consider the overall condition only if A rewards 1500 and D rewards 1500.

    I would have used TRUE*Number as Wilson said.

  6. Cyril Z. says:

    oops, forgot the return ;

    `

    =IF((IF(C4=0,1500,IF(C4″”,F4″”,G4>=1),1000,0)
    +IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))=5000,10000,
    IF(C4=0,1500,IF(C4″”,F4″”,G4>=1),1000,0)
    +IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))
    `

  7. Cyril Z. says:

    Ok, it’s the double quote’s fault.

    here it is :

    `
    =IF((IF(C4=0,1500,IF(C4””,F4””,G4>=1),1000,0)
    +IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))=5000,10000,
    IF(C4=0,1500,IF(C4””,F4””,G4>=1),1000,0)
    +IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))
    `

    Sorry for spamming..

    Chandoo could you please remove all previous comments ?

  8. Cyril Z. says:

    =IF((IF(C4=0,1500,IF(C4<0.03,1000,0))
    +IF(OR(E4<500,AND(F4<>””,F4<560)),1000,0)
    +IF(AND(G4<>””,G4>=1),1000,0)
    +IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))=5000,10000,
    IF(C4=0,1500,IF(C4<0.03,1000,0))
    +IF(OR(E4<500,AND(F4<>””,F4<560)),1000,0)
    +IF(AND(G4<>””,G4>=1),1000,0)
    +IF(D4>=0.98,1500,IF(D4>=0.96,500,0)))

  9. Peter says:

    I seperated all the validations. Only set the true value. If needed a false value could be addded.

    =IF(C496%,500)+IF(D4>98%,1000)+IF(OR(E4=1,1000)

  10. Peter says:

    ‘=IF(C496%,500)+IF(D4>98%,1000)+IF(OR(E4=1,1000)’

  11. Greg says:

    it appears on your formula the phone and fax never having both condition is not working. Here is my solution.

    =IF(C4=0,1500,IF(C4>=0.03,1000,0))+IF(AND(E4=1,1000,0)+IF(AND(D4>=0.98,D4=0.96,D4

    • Greg says:

      it appears the formula is getting cut off after it’s been posted. here is another try.
      =IF(C4=0,1500,IF(C4>=0.03,1000,0))+IF(AND
      (E4=1,1000,0)+IF(AND(D4>=0.98,D4=0.96,D4

  12. Greg says:

    You should do more challenges like this. I really like them, really tests my skills outside of my profession.

  13. Luke M says:

    No IF functions at all, and shorter than Chandoo’s. Note that it looks longer than it is really because I had to replace all the greater than and less than symbols with words to post.

    =(C4[less than]3%)*1000+(C4=0)*500+(G4[greater than]0)*1000+(D4[greater than or equal to]96%)*500+(D4[greater than or equal to]98%)*1000+((E4[less than]500)+(F4[less than]560)[greater than]0)*1000

  14. Matt says:

    I don’t know why but Chanddo solution gives a wrong answer on my excel. The commendation condition (G4>=1) is TRUE even if G4 is blank, therefore giving php 1000 everytime (because the cell is either blank or =1). I have no idea why this happens.

    My solution is the one of a somebody who just discovered excel and programming and therefore lacks beauty… Here it is:

    =IF(AND(C12>0;C120,98;1500;IF(D12>0,96;500))

    To my understanding, nobody in the example qualifies for every condition so nobody should have the php 5000. The sum of all conditions met is 5000 anyway.

  15. Yash says:

    One day I rated myself in excel between 5-6 (on a scale of 10)….when i came to know about your website from one of my friends then my rating fell down day by day ( by seeing your posts)….Now I rate myself somewhere around 0.1 out of 10.
    Thanks for your amazing posts and you are making me learn new things everyday….Bless…..

  16. daversmitty says:

    *I noticed one problem with the formula Chandoo offered, besides the data in column G not returning 0 when blank.  For the data in column D the formula says that if greater than 96% then 500 Php are given.  This does not take into account anyone who receives 96%.  It should be greater than or equal to.  This is causing the second employee to be short by 500 Php.

    • GregM says:

      I agree with your notes on Chandoo’s solution errors. His calculations for rows 5, 7, 8, 9, and 11 have wrong values.

      My solution:
      =500*(C4 = 0) + 1000*(C4 < 0.03) + 1000*OR(AND(E4 > 0,E4 < 500),AND(F4 > 0,F4 < 560))+1000*(G4 > = 1)*(G4 < > “”)+500*(D4 > = 0.96)+1000*(D4 > = 0.98)

  17. Chandoo says:

    Testing formulas.

    =(C4<3%)*1000 +(C4=0%)*500 +IF(AND(E4<>”",E4<500),1000,IF(AND(F4<>”",F4<560),1000,0))+ (G4>=1)*1000+(D4>96%)*500+(D4>=98%)*1000

  18. Jamie Bull says:

    Got it down to 113 characters.
    =100*((C4&lt;=0.03)*10
    +(C4=0)*5
    +(D4&gt;=0.96)*5
    +(D4&gt;=0.98)*10
    +OR(AND(E4 &lt;500,E4&lt;gt;0),AND(F4&lt;560,F4&gt;0))*10
    +(G4&gt;=1)*10)

  19. Hypnos says:

    =IF(C4=0, 1500, IF(C4<3%,1000))+IF(E4<500,1000,IF(F4<560,1000))+IF(G4=1,1000)+IF(D4>98%,1500, IF(D4>96%, 500))+IF(AND(C4<3%, OR(E4<500, F4<560), G4=1, D4>96%), 5000)

  20. Martin says:

    testing:

    =(C4=0)*1500+(C4<3%)*(C4>0)*1000+(E4<500)*1000+(F4<560)*1000+(G4>0)*1000+(D4>=98%)*1500+(D4>=96%)*(D4<=97,99%)*1500+(C4=0)*(C4<3%)*(E4<500)*(F4<560)*(G4>0)*(D4>=96)*5000

    I know, there is no IF…..

  21. Martin says:

    some questions:
    conditions 3 and 4: are mutually exclusive (if one is met, the other not)? if both can be met, which wins?
    condition 8 means cond 1 OR cond2 AND cond3 OR cond4 AND cond5 AND cond6 OR cond7 ?

    great thing to use boolean instead of formulas… soooo easy to understand !!!

  22. Cyril Z. says:

    To all,

    G4>=1 doesn’t work as expected, because it is TRUE when G4 is empty (XL2007)

    G4=1 is only acceptable if the possible values are 1 or null.

    Regards…

    • Luke M says:

      Nice catch Cyril. Looks like col G has some sort of
      =”"
      type result, has the ISBLANK function also returns false. Perhaps this then?
      =(C4<3%)*1000+(C4=0)*500+(D4>=96%)*500+(D4>=98%)*1000+((E4<500)+(F4<560)>0)*1000+ISNUMBER(G4)*1000 

  23. Martin says:

    Defining Named Ranges as follows, the result is all_cond

    Name
    RefersToLocal

    all_cond
    =SUM(cond1;cond2;cond3;cond4;cond5;cond6;cond7;cond8)

    cond1
    =(‘if-formula’!$C4=0)*1500

    cond2
    =(‘if-formula’!$C4>0)*(‘if-formula’!$C4<3%)*1000

    cond3
    =(‘if-formula’!$E4<500)*1000

    cond4
    =(‘if-formula’!$F4<560)*(‘if-formula’!$F4>0)*1000

    cond4or3
    =OR(cond4;cond3)

    cond5
    =(‘if-formula’!$G4>=1)*1000

    cond6
    =(‘if-formula’!$D4>98%)*1500

    cond7
    =(‘if-formula’!$D4>=96%)*(‘if-formula’!$D4<=97,99%)*500

    cond8
    =(cond1/1500+cond2/1000)*(cond3/1000+cond4/1000)*cond5/1000*(cond6/1500+cond7/500)*5000

  24. Ben Niebuhr says:

    My version:

    =([Attendance] LT 0.03)*1000+([Attendance] LT =0)*500
    +OR(AND([HT Voice] LT 500,ISNUMBER([HT Voice])),AND([HT Fax] LT 560,ISNUMBER([HT Fax])))*1000
    +ISNUMBER([Commendation])*1000
    +([Quality of Audit] GE 0.96)*500+([Quality of Audit] GE 0.98)*1000

    This could be simplified if the blanks in the commendation and handling time fields where truely blanks, as others have noted.

    Thanks for the challenge, Chandoo. These are very educational.

  25. Chip says:

    =IF(C4=0,1500,IF(C4 < 0.03,1000,0))
    +MAX(IF(E4 < 500,1000,0)+IF(F4 < 560,1000,0))
    +IF(G4 < 1,0,1000)
    +IF(D4 < 0.96,0,IF(D4 < 0.98,500,1500))

    • Chip says:

      Forgot to replace ‘+’ with ‘,’ on MAX line

      =IF(C4=0,1500,IF(C4 < 0.03,1000,0))
      +MAX(IF(E4 < 500,1000,0),IF(F4 < 560,1000,0))
      +IF(G4 < 1,0,1000)
      +IF(D4 < 0.96,0,IF(D4 < 0.98,500,1500))

  26. Allan González says:

    =SI(C4=0%,1500,SI(Y(C4<3%,C4>0%),1000,0))+SI(E4<500,1000,SI(F4<560,1000,0))+SI(G4>0,1000,0)+SI(Y(D4>=98%,D4<=100%),1500,SI(Y(D4>=96%,D4<=97.99%),500,0))

  27. cALi says:

    Hi to all,
    This is my solution:
    = (C4 = 0)*500+(C4 < 3%)*1000+(D4 > = 96%)*500+(D4 > = 98%)*1000+MAX((E4 < > “”)*(E4 < 500)*1000,(F4 < > “”)*(F4 < 560)*1000)+(G4 > = 1)*1000

    Nice test! 

  28. David Ruggles says:

    My try with one if statement. 
    I added spaces in between > = and < > to appear correctly here, but need to be removed to work in excel.
    =(C5 < 3%)*1000
    +(C5 = 0%)*500
    + IF((E5 < > “”)*(E5<500),1000, (F5 < > “”)*(F5 < 560)*1000)
    + (G5 > = 1)*1000
    +(D5 > 96%)*500
    +(D5 > = 98%)*1000

  29. Mayank Bhatia says:

    Here is my solution, each condition is a separate if. I like the solution in the sheet, but it doenst check for G4<>”" and gives incorrect answers. I also added one more IF to check that the total doesnt exceed 5000. IT does if all the conditions are met at highest payouts (Php6000). So the big IF on top caps it to 5000.

       =IF(IF(C4=0%,1500,IF(C4 < 3%,1000,0))
    +IF(E4 < 500,1000,0)
    +IF(AND(F4 <> “”,F4 < 560),1000,0)
    +IF(AND(G4 <> “”,G4 > = 1),1000,0)
    +IF(AND(D4 > 98%,D4 <= 100%),1500,IF(AND(D4 > 96%,D4 < 98%),500,0))
      >=5000, 5000, 
    IF(IF(C4=0%,1500,IF(C4 < 3%,1000,0))
    +IF(E4 < 500,1000,0)
    +IF(AND(F4 <> “”,F4 < 560),1000,0)
    +IF(AND(G4 <> “”,G4 > = 1),1000,0)
    +IF(AND(D4 > 98%,D4 <= 100%),1500,IF(AND(D4 > 96%,D4 < 98%),500,0)))

  30. Alan says:

    I had to cheat, but only after I had done most of it but could not get it to work.
     
    Could you send another, but this time not provide the answer?

  31. Elias says:

    One more,

    =500*(C4=0)+1000*(C4 < 3%)
    +MAX((E4 < 500)*1000,(F4 < 560)*1000)
    +1000*(G4>0)
    +500*(D4 > =96%)+1000*(D4 > =98%)

    Regards

  32. =IF(C4=0,1500,IF(C4 < 0.03,1000,0))+IF(OR(AND(E4 < > “”,E4 < 500),(AND(F4 < > “”,F4 < 560))),1000,0)+IF(AND(G4 < > “”,G4 > =1),1000,0)+IF(D4 > =0.98,1500,IF(D4 > =0.96,500,0))

  33. DaveD says:

    =SUM(IF(C4=0,1500,IF(C4 < 0.03,1000,0)),IF(D4 > =0.98,1500,(IF(D4 < 0.96,0,500))),IF(OR(AND(E4 < 500,E4 < > “”),AND(F4 < 560,F4 < > “”)),1000,0),IF(G4=1,1000,0))

  34. Tiho says:

    Keep it simple!

    =IF(C5=0,1500,IF(AND(C5>0,C5<3%),1000,0))  “Attendance”
    +IF(E5<500,1000,0)  “Voice”
    +IF(F5<560,1000,0)  “Fax”
    +IF(D5>=98%,1500,IF(AND(D5>=96%,D5<=97.99%),500,0)) “Quality”

    • Tiho says:

      Sorry, missed the commendation….
      Just add a MIN formula then

      =MIN((IF(C15=0,1500,IF(AND(C15>0,C15<3%),1000,0))+IF(E15<500,1000,0)+IF(F15<560,1000,0)+IF(D15>=98%,1500,IF(AND(D15>=96%,D15<=97.99%),500,0))+IF(G15=1,1000,0)),5000)

  35. Michael says:

    Hi Chandos,
    Great use of ‘True’. Never thought of using it this way.
    I am new to your blog and found this interesting.
    The solution:
    = IF ( C4 = 0 , 1500 , IF ( AND ( C4 > 0 , C4 < = 0.03 ) , 1000 , 0 ) ) + IF ( D4 > = 0.98 , 1500 , IF ( AND ( D4 < 0.98 , D4 > = 0.96 ) , 500 , 0 ) ) + IF ( AND ( E4 < > 0 , E4 < 500 ) , 1000 , IF ( AND ( F4 < > 0 , F4 < 560 ) , 1000 , 0 ) ) + IF ( G4 > = 1 , 1000 , 0 )
    Your solution doesn’t seem to work for D4 when it is 96% as your formula states “(D4<96%)*1000″ which is not correct as the problem posed says “96% – 97.99%”.
    Blessed Love,

  36. Ruben says:

    I really “translated” the conditions literally here:

    =IF(C4 = 0%,1500,IF(C4 < 3%,1000,0))
    +IF(OR(AND(E4 <> “”,E4 < 500),AND(F4 <> “”,F4 < 560)),1000,0)
    +IF(AND(G4 <> “”,G4 >= 1),1000,0)
    +IF(D4 >= 98%,1500,IF(D4 >= 96%,500,0))

    I guess we could add a MIN(formula,5000) to ensure the 5th condition, although a quick analysis shows it’s not needed.
    Getting the same results, except for the bug when Commendation = “” mentioned above…

  37. Ruben says:

    do I get the job?! :)

  38. Christian V. says:

    Chandoos formula seems not to work. Why are the empty cells in column G not empty? If I delete the invisible content of column G Chandoos formula works.

    My take is that data must have been copied and pasted from another source than Excel. In this case Chandoos formula could’ve been saved by either a short macro or by making the formula just a little bit longer using n() e.g. …. (N(G7)>=1)*1000

    Even though Chandoo puts a great effort in this blog I didn’t catch that the phone fax condition was either one or the other by reading the criterias given. 

  39. Debajyoti says:

    Dear Chandoo,

    My Solution: -
    Php= sum(
    IF(C4=0%,1500,IF(AND(C4 < 3%,C4 > 0%),1000,0)),IF(OR(AND(IF(E4 < 500,1000,0),IF(F4 < 560,1000,0)),OR(IF(E4 < 500,1000,0),IF(F4 < 560,1000,0))),1000,0),IFERROR(IF(ABS(G4) > 0,1000,0),0),IF(D4 > 96%,IF(D4 > 97.99%,1500,1000),0))

    The use of the ABS func is required since some items in the commendation column were not numbers.

    Cheers, 
    Debajyoti 

  40. Rod says:

    =IF(C4=0,1500,IF(C4<0.03,1000,0))+IF(G4=”",0,IF(G4>=1,1000))+IF(D4>=0.98,1500,IF(D4>=96,500,0))+IF(E4<=500,1000,IF(F4<=560,1000,0))

  41. John Andrew says:

    =IF(C4 = 0,1500,IF(C4 < 3%,1000,0)) + IF(D4 > 96%,500,IF(D4 > 97.99%,1500,0)) + MAX(IF(and(E4>0,E4 < 500),1000,0) , IF(and(F4>0,F4 < 560),1000,0)) + IF(ISNUMBER(G4),1000,0) 

    Its a common problem where blanks are non necessarily blanks, and they carry into formulas as text rather than numberic values.  In this case commendations (col G) are not entered are left blank, then just checking for a numeric entry is all that is needed.  Ditto for phone/fax times left blank – in that instance, check for non-zero value.    

  42. 123rgc says:

    I agree with daversmitty who points out that there is an error in model answer, as the formula (G4>=1)*1000 always returns 1000 php, even if the Commendation is less than 1.  I would also interpret the quality as paying from 96% and not 96%+

    From experience, I also dislike coding lots of IF formula into a single cell, as it makes auditing difficult, and assumptions will ALWAYS change as soon as you have completed the model. This may be either the trigger points or the Php amounts.

    It would therefore be safer to use an Assumptions table, Range Name each section and then use VLOOKUP for each element and calculate separately before aggregating.  If you set the table out first, it will take the top right cell as the Range Name.

    =VLOOKUP($C4,Attendance,2,TRUE)
    =MAX(VLOOKUP($E4,P_Handling,2,TRUE),VLOOKUP($F4,F_Handling,2,TRUE)) 
    =IF($G4=”",0,(VLOOKUP($G4,Commend,2,TRUE))) 
    =IF($G4=”",0,(VLOOKUP($G4,Commend,2,TRUE))) 

    Then add all 4 elements for the bonus.

    You can then show the result graphically as follows:

  43. 123rgc says:

    My pastes on my post did not upload.  Here is the raw data:

    Attendance
    Php
    P Handling
    Php
    F Handling
    Php
    Commend
    Php
    Quality
    Php

    0.00%
    1500
    0
    1000
    0
    1000
    0
    0
    0.00%
    0

    3.00%
    1000
    500
    0
    560
    0
    1
    1000
    96.00%
    500

    3.01%
    0
     
     
     
     
     
     
    98.00%
    1500

    v\:* {behavior:url(#default#VML);}
    o\:* {behavior:url(#default#VML);}
    x\:* {behavior:url(#default#VML);}
    .shape {behavior:url(#default#VML);}

    Employee Number
    Attendance
    Handling
    Commend
    Quality
    Bonus

    EMP0001
    1500
    1000
    1000
    0
    3500

    EMP0002
    0
    1000
    1000
    500
    2500

    EMP0003
    0
    1000
    1000
    0
    2000

    EMP0004
    1500
    1000
    0
    500
    3000

    EMP0005
    0
    1000
    0
    1500
    2500

    EMP0006
    0
    1000
    0
    0
    1000

    EMP0007
    0
    0
    1000
    500
    1500

    EMP0008
    0
    1000
    0
    500
    1500

    EMP0009
    1500
    1000
    0
    1500
    4000

    EMP0010
    0
    1000
    1000
    0
    2000

  44. Paul Radion says:

    Hello Chandoo,

    I disagree with your solution - your formula doesn’t provide a reward if the quality of the audit is 96%, though the condition is stated as “If the quality audit is 96% -97.99 % reward will be Php 500″  Here is my solution:

    “=IF(C4=0,1500,IF(C4<0.03,1000,0))+IF(G4<>0,1000,0)+IF(D4>=0.96,500,0)+IF(D4>=0.98,1000,0)+IF(OR(E4<500,F4<560),1000,0) ”

    Drop the beginning and end quotes when using it in excel. 

  45. Kyle McGhee says:

    =SUMPRODUCT((A2<=C4:C5)*D4:D5+(B2<C7:C8)*(C2=E7:E8)*D7:D8+(D2>0)*D10:D11+(E2>=C13:C14)*D13:D14)

  46. Linda says:

    =(C4 < 0.03)*1000+(C4=0)*500+OR(AND(E4 < 500,E4 <> 0),AND(F4 <> 0,F4 < 560))*1000+(G4 > 0)*1000+(D4 >=0.96)*500+(D4 >= 0.98)*1000

  47. Dennis Del Villar says:

    here is my solution:
    =if(c4<=0,1500.0)
    +if(and(c4>0,c4<3%),1000,0)
    +if(g4=1,1000,0)
    +if(d4>98%,1500,0)
    +if(and(d4>=96%,d4<=97.99%),500,0)
    +if(or(e4<500,f4<560),1000,if(f4<560,1000,0))
     
     

  48. Alicia says:

    I grouped mine together, criteria for column 1, 2, etc.

    =IF(C4=0,1500,IF(C4 < =0.03,1000,0))+
    IF(D4 > =0.98,1500,IF(D4 > 0.96,500,0))+
    IF(OR(E4 < =500,F4 < =560),1000,0)+
    IF(G4 > 0,1000,0)

  49. Daniel says:

    There we go…

    I guess that your formula at the file is not right since quality audit of 96% according with the text should be considered to the Php…

    My version…

    =IF(C4=0;1500;IF(C4< 0,03;1000;0))+MAX(IF(E4< 500;1000;0);IF(F4< 560;1000;0))+IF(G4> =1;1000;0)+IF(D4> =0,98;1500;IF(D4> =0,96;500;0))

  50. Deepa says:

    Hi Chandoo,

    My solution : =IF (C4 = 0% ,1500 ,IF (C4 < 3% ,1000,0))+ IF( E4 < 500,1000, IF(AND (F4 <> “”,F4 < 560), 1000, 0))+IF( AND( G4 <> “”, G4 > 0),1000,0)+IF(D4 >= 98%,1500,IF( AND( D4 > 96%, D4 <= 97.99%),500,0))

    I think you have an error in your Commendation formula. It returns 1000 even if there are no commendations

  51. sagar says:

    Just on initial thought, came up with this lengthy answer:

    =IF(C4=0,1500,IF(C4<3%,1000,0))
    +IF(D4>=98%,1500,IF(AND(D4>=96%,D4<98%),500,0))
    +IF(AND(E4<500,E4<>”"),1000,0)+IF(AND(F4<560,F4<>”"),1000,0)
    +IF(G4<>”",1000,0)

    • sagar says:

      Minor change to the formula:
      =IF(C4=0,1500,IF(C4<3%,1000,0))
      +IF(D4>=98%,1500,IF(AND(D4>=96%,D4<98%),500,0))
      +MAX(IF(AND(E4<500,E4<>”"),1000,0),IF(AND(F4<560,F4<>”"),1000,0))
      +IF(G4<>””,1000,0)

  52. Nags says:

    =+IF(C4>3%,0,IF(C4=0%,1500,1000))+IF(E4>F4,IF(E4<500,1000,0),IF(F4<560,1000,0))+IF(G4>0,1000,0)+IF(D4>97.99%,1500,IF(D4>95.99%,500,0))

  53. mikemerson says:

    =IF($C4 = 0,1500,IF(AND($C4 > 0,$C4 < 0.03),1000,0))+IF(OR($E4 < 500,$F4 < 560),1000,0)+IF($G4 > 0,1000,0)+IF($D4 >= 0.98,1500,IF(AND($D4 > 0.96,$D4 < 0.98),500))

  54. Anni says:

    Hi Chandoo,

    My solution

    IF(C4 = 0%,1500,IF(C4 < 3%,1000,0))+IF(D4 < 96%,0,IF(D4 < 97.99%,500,IF(D4 > 100%,0,IF(D4 > = 96%,1500))))+IF(G4 > = 1,1000,0)+IF(E4 < 500,1000,IF(F4 < 560,1000,0))

  55. Arjun Rao says:

    here is my formula
    =IF(G4>=1,1000,0)+IF(C4=0%,1500,(IF(C4<3%,1000,0)))+IF(D4>=98%,IF(D4<=100%,1500,0),IF(D4>=96%,IF(D4<=97.99%,500,0),0))+IF(E4<500,1000,IF(F4<560,1000,0))
    I really amazed after looking at your solution. Its amazing…!
     

  56. Rohit1409 says:

    Hi Chandoo,

    Nice Challenge !!!

    Your EMP0002 total doesn’t look right, the payout should be 2500, where as your answer says 2000. Rest all my answers match with yours.

    Here is my formula (though a bit lengthy but satisfy all conditions :)

    “=IF(C4 >  3%,0,IF(AND(C4 < =3%,C4 >  0%),1000,IF(C4=0%,1500,0)))+IF(OR(E4 < 500,F4 < 560),1000,0)+IF(G4 >  =1,1000,0)+IF(D4 < 96%,0,IF(AND(D4 >  =96%,D4 < 98%),500,IF(D4 >  =98%,1500,0)))”

    Note: I have added space before and after  >   <  as suggested.

    Cheers,

    Rohit1409 

  57. Magesh Kumar says:

    =IF(C4=0%,1500,IF(C4 < 3%,1000,0))+IF(OR(E4 < 500,F4,560),1000,0)+IF(G4 > =1,1000,0)+IF(AND(D4 > =98%,D4 < =100%),1500,IF(AND(D4 > =96%,D4 < =97.99%),500,0))

    • Magesh Kumar says:

      the corrected formula is as below

      =IF(C4=0%,1500,IF(C4 < 3%,1000,0))+IF(OR(E4 < 500,F4 < 560),1000,0)+IF(G4 > =1,1000,0)+IF(AND(D4 > =98%,D4 < =100%),1500,IF(AND(D4 > =96%,D4 < =97.99%),500,0))

  58. Oli says:

    Hi Chandoo

    I was late to the party but I think there is an error in your formula for the criteria testing the ‘comendation’…. (someone may have already pointed this out but I didn’t have time to read all the comments on here).

    So here is my solution based on how I understood the problem:

    =((B2<3%)*1000)+((B2=0)*500)
    +(OR(D2<500,E2<560)*1000)
    +(ISNUMBER(F2)*1000)
    +((C2>=96%)*500)+((C2>98%)*1000)

    Thanks
    Oli

    • Oli says:

      * correction * commendation

      It seems I have over parenthesized – so here is a more concise version:

      =(B12<3%)*1000+(B12=0)*500
      +OR(D12<500,E12<560)*1000
      +ISNUMBER(F12)*1000
      +(C12>=96%)*500+(C12>98%)*1000

  59. Ian C says:

    Hi,

    Heres my answer. I never knew about the (C4 = 0) = True trick either! Its Fantastic!

    =IF(C3=0%,1500,IF(C3 < 3%,1000,0))
    +IF(OR(G3 < 500,I3 < 560),1000,0)
    +(1000*COUNT(K3))
    +IF(E3 > =98%,1500,IF(E3 > =96%,500,0))

    Also, Chandoo’s formula for calculating the  commendation payout does not work correctly. I this because i am using Excel 2003?

    Thanks,
    Ian

  60. Nuruddin says:

    Hi Chandoo,

    There is diffrence between your answer and mine on Emp 002, reasoning quality of audit.

    Condition mentioned listed below does not say quality audit less than 96%, it means 96% to 97.99%.
    •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
    My version of formula listed below:

    (IF($C4=0,1.5,IF($C4<0.03,1,0))+IF(OR($E4<500,$F4<560),1,0)+IF($G4>=1,1,0)+IF(AND($D4>=0.96,$D4<0.98),0.5,IF(AND($D4>=0.98,$D4<=1),1.5,0)))*1000

  61. Innis says:

    =IF(C1=0,1500,IF(C1<3,1000))+IF(AND(C2<500,D2=”Phone”),1000,IF(AND(C2<560,D2=”Fax”),1000,0))+IF(C3>0,1000,0)+IF(AND(C4>=96, C4<=97.99),500,IF(AND(C4>=98, C4<=100),1500))

  62. Robert Clark says:

    My solution – no IFs!

    =(C4 < 0.03) * 1000 + (C4 = 0) * 500 + (D4 >= 0.96) * 500 + (D4 >= 98) * 1000 +  (OR((E4 <> “”) * (E4 < 500),(F4 <> “”) * (F4 < 560))) * 1000 + (G4 >= 1) * 1000

  63. Chiquitin says:

    Hi everyone:

    Here is my solution

    =(C4 < 0,03)*1000+(C4=0)*500+(D4 < 0,98)*-500+(D4 < 0,96)*-1000+1500+MAX(AND(E4 < > “”,E4 < 500)*1000;AND(F4 < > “”;F4 < 560)*1000)+AND(G4 < > “”;G4 > = 1)*1000

    There is only one problem, I have not used IF

  64. Manoj says:

    = SUM(IF(C4=0,1500,IF(C4 >0.3,1000,0)),IF(AND(E4 < >”",E4 <500),1000,IF(AND(F4 < >”",F4 <560),1000,0)),IF(G4 >=1,1000,0),IF(D4 >0.98,1500,IF(D4 >0.96,500,0)))
     
    I am convenience with the formula, but it is not matching with the answer in row 7. Rest all answers are matching..!

  65. Faseeh says:

    Hi all,
    Have i misunderstood it or there is some mistake in that. In chandoo’s provided file, the reward for Attendance = 0% is 1500 while he is multiplying his formula in C4 with 500?? Am I missing something?
    Faseeh

  66. Uwe says:

    Well, I have split up all the formulas and here they come:
    =IF(C4=0;1500;IF(C4 < 3%;1000;0))
    +IF(AND(D4 > =96%;D4 < 97,99%);500;IF(D4 > =98%;1500;0))
    +IF(OR(E4 < 500;F4 < 500);1000;0)
    +IF(G4 > =1;1000;0)
    +IF(AND(C4 < 3%;D4 > =96%;OR(E4 < 560;F4 < 560);G4 > =1);5000;0)
    Attendance:
    =IF(C4=0;1500;IF(C4 < 3%;1000;0))
    Quality of Audit
    +IF(AND(D4 > =96%;D4 < 97,99%);500;IF(D4 > =98%;1500;0))
    Handling Time
    +IF(OR(E4 < 500;F4 < 500);1000;0)
    Commendation
    +IF(G4 > =1;1000;0)
    Bonus
    +IF(AND(C4 < 3%;D4 > =96%;OR(E4 < 560;F4 < 560);G4 > =1);5000;0)+IF(G4 > =1;1000;0)
    I am not sure whether the 5000 Bonus was meant as a bonus or whether the total sum should be 5000 max. I think the bonus would be more fair in that system because an overall good performance might be better than just someone who is outperforming in one field of action. :-)
    I think this is the easiest way to show it for people to easily understand it, while it might not be the fastest way to calculate it. But if you look at the example, which is already showing aggregated data, the speed seems not to be that important.
    The table seems to be some kind of formatted, so I would suggest that each cell should be opened by chosing each cell, pressing “F2″ and enter to remove any blanks etc… If not, you might get problems e.g. with the calculation of column “G” commendation.
    Who does not like that option (It could be time consuming if you use bigger tables) can also use the integrated “Text in columns” option in the menu, to make sure that data has been cleared.
    Cheers, Uwe

  67. Kris says:

    =(
    (IF(C4=0,1500,(IF(C4<3,1000,0))))+
    (IF(E4<500,1000,(IF(F4<560,1000,0))))+
    (IF(G4>=1,1000,0))+
    (IF(D4>=98,1500,(IF(D4>=96,500,0))))
    )

  68. Pablo says:

    Hello,
    Here is the formula.
    =IF(C4=0,1500,IF(C4 < 3%,1000,0)) + IF(OR(E4 < 500,F4 < 560),1000,0) + IF(G4 > = 1,1000,0) + IF(D4 > = 98%,1500,IF(D4 > = 96%,500,0))
    With this logic, no need to use AND.
    Pablo

  69. Chandoo says:

    @Dinesh, Daversmitty, Paul, DaveM, Faseeh, Nuruddin, Ian C

    Thanks for pointing out the mistake in my solution. I omitted = symbol and assumed that commendation cell is a number.

     

    @All: many thanks for enthusiastic participation. I am learning quite a few tricks from your implementation. Keep them coming.

  70. Godsbod says:

    Lots of solutions in here that do the job.
    I did notice that Chandoo solutions had errors and these have been pointed out, not sure why line 12 shows 5000 in his result as there is nothing in cell G12, but if I replace the “” with a zero, then delete it, the formula gives the correct answer.

    I have learned so much from this exercise. I always struggled with logic functions like ‘AND’ and ‘OR’, but now I understand them. Using a TRUE/FALSE statement as part of a numerical formula is new to me… that’s not in many books.

    Anyways, here is my solution:

    =IF(C4=0,1500,IF(C4< 0.03,1000,0))+IF(AND(E4< > “”,E4<500),1000,IF(AND(F4< > “”,F4< 560),1000,0))+IF(D4> =0.98,1500,IF(AND(D4>  =0.96,D4< =0.9799),500,0))+IF(AND(G4> 0,G4< > “”),1000,0)

    Hopefully I have put enough spaces in this to work here.

  71. Jamie Bull says:

    102 characters. Can anyone see a way to get it under 100?

    =100*((C4<=3%)*10+(C4=0)*5
    +(D4>=96%)*5+(D4>=98%)*10
    +MAX(E4<500*(E4>0),F4<560*(E4>0))*10
    +(G4>=1)*10)

    • cALi says:

      Jamie,
      92 characters:
      =1000*((C4 < =3%)+(C4=0)*0.5+(D4 > =96%)*0.5+(D4 > =98%)+MAX(E4 < 500*(E4 > 0),F4<560*(E4 > 0))+(G4 > =1))

      (remove any space to make it work) 

      • cALi says:

        BTW, if you try to replace 0.5 to .5, excel will replace it to 0.5 again…

        • Elias says:

          @ Jamie,

          75 Chr.

          =(
          ((C4 < = 3%)+
          ((C4 = 0)+(D4 > = 96%))*0.5
          +(D4 > = 98%))
          +OR(E4 < 500,F4 < 560)
          +(G4 > 0)
          )*1000

          Regards

          • Luke M says:

            Just realized that if we use SUM, can get rid of a bunch of parenthesis.
            69 Chr.
             

            =SUM(SUM(C4=0,D4>=96%)*0.5,C4<3%,D4>=98%,OR(E4<500,F4<560),G4=1)*1000 

    • Jeanbar says:

      90 chars using your formula (congrats for your formula):
      1000*((C4<=3%)+(C4=0)/2
      +(D4>=96%)/2+(D4>=98%)
      +MAX(E4<500*(E4>0);F4<560*(E4>0))
      +(G4>=1))

  72. Luke M says:

    67 Chr by changing the *0.5 bit.
    =SUM(SUM(C4=0,D4>=96%)/2,C4<3%,D4>=98%,OR(E4<500,F4<560),G4=1)*1000 

  73. Elias says:

    @Luke, That’s a good one.
    Just adjust your formula to C4 < = 3% and G4 > 0
    to get the correct results.

    Regards

    • Luke M says:

      @Elias,
      Per the instruction, need to be less than 3%
      “If the attendance is less than 3% then they will earn Php 1000 only”

      Also, the commendation column contains cells with “”, which gets counted as >0, hence the G4=1.

  74. Christian V. says:

    Regarding the error in Chandoos formula. You have to take into account how the raw data is received, meaning you have to adapt your formula to the given dataset. The solution to the error is to add N() to the formula like this: (N (G4) <= 1 )*1000 etc.

  75. Elias says:

    @Luke, you’re right on the < 3%, but the other one would safer to use
    N(G4)>0 in case they are more than 1 Commendation.

    Regards

  76. Mike Larbalestier says:

    =IF(C4 < 3%,IF(C4 < > 0%,1000,1500),0)+IF(E4 < > “”,IF(E4 < 500,1000,IF(F4 < > “”,IF(F4 < 560,1000,0))))+IF(G4 < > “”,IF(G4 > =1,1000,0),0)+IF(D4 > =96%,IF(D4 > =98%,1500,500),0)

  77. Christian V. says:

    I agree with you guys on 96% as the lower limit thus > = 96%

    In addition I believe that the error in the formula regarding column G should be taken into account when writing the formula to calculate PHP. I mean if this would be a returning task you would get the same type of data wiht cells looking empty althoug they’re not. Solving this by formula only could be done using the N() function which converts cell contents to numbers and the seemingly empty cell will return a zero like this

    = (((C4 = 0%)+(D4 > 98%)) * 1.5+AND(C4 < 3%,C4 > 0%)+OR(E4 < 500,F4 < 560)+(N(G4 ) > = 1)+AND(D4 > = 96%,D4 < 98%) * 0.5) * 1000 

  78. Jamie Bull says:

    86 characters, using just OR instead of OR and AND.

    =1000*SUM(SUM(C5=0,D5>=96%)/2,D5>=98%,C5<=3%,OR((E5<500*(E5>0)),(F5<560*(F5>0))),G5>0) 

    Also, I hadn’t realised that LEN counts line breaks as characters.

  79. Raluca says:

    Hello, 

    this is my solution…it works so i’m happy about it :)

    =SUM(IF(C4=0%,1500,IF(C4 < 3%,1000)),IF(98% < =D4 < =100%,1500,IF(96% < =D4 < 98%,500)),IF(OR(E4 < 500,F4 < 560),1000),IF(G4 > =1,1000)) 

     

  80. Rosco says:

    And here’s mine:
     
    =IF(C4<3%,IF(C4=0,1500,1000),0)
    +IF(E4>=500,IF(F4<>”",IF(F4>560,0,1000)),1000)
    +IF(G4>0,1000,0)
    +IF(D4>=98%,1500,IF(D4>=96%,500,0))
     
    Thanks Chandoo. Really enjoyed the challenge. Would be great to have more.
     

  81. Roger L Moreno says:

    Hello, my first post. Loved this challange. I have observed that the bonus part of the challange has not been considered by many of the solutions.
    Attendance            (C4=0)*500+(C4<0.03)*1000 +
    Quality Audit          IF(D4>=0.98,1500,IF(D4>=0.96,500,0)) +
    Voice or Fax           IF(OR(E4<500,F4<560),1000,0) +
    Recommendation   (G4>0)*1000 +
    Bonus (4 out of 5)  IF(IF(C4=0,1,IF(C4<0.03,1,0))+(D4>=0.96)*1+IF(OR(E4<500,F4<560),1,0)+(G4>0)*1=4,5000,0)
    Total bonus computed: $23,000

  82. Teamerat says:

    My first Post. I like the challenge. Here is the IF formula:

    =IF(Attendance=0,1500,IF(AND(Attendance > 0,Attendance < 3),1000,0)) + IF(Commendation >= 1,1000,0) + IF(OR(Handling Time (Voice) < 560,Handling Time(Fax) < 500),1000,0) + IF(AND(Quality >= 96%,Quality <= 97.99%),500,IF(AND(Quality >= 98%, Quality <= 100%),1500,0))

  83. Dheeraj says:

    here is my formula:
     
    =IF(C4=0%,1500,IF(AND(C4>0%,C4<3%),1000,0))+IF(D4>=98%,1500,IF(AND(D4>=96%,D4<98%),500,0))+IF(G4>=1,1000,0)+IF(OR(E4<500,F4<560),1000,0)
    Only in “Commendation” field I had to retype data.
    Total CashOut is 21000.

  84. Mauricio says:

    It has happened to me several times to deal with a situation where an if-formula should return TRUE if a cell contained one of various values. In other words if ( cell_tested = 1 or 2 or 3, TRUE, FALSE). There’s an obvious solution using or-formula (if ( or ( cell_tested = ; cell_tested = 2; cell_tested 3); TRUE; FALSE)). But, when you are combining if-functions with procv, index, match, repeating the value tested becomes a hassle. So, after a time thinking about it and a few tries, I thought of a nicer solution that would be if ( sumproduct ( (cell_tested = transpose{1; 2; 3})*(1)); TRUE; FALSE). It works exactly the same way as if using the combination of if and or formulas and its easier to understand. Plus, you can also replace the transpose{1; 2; 3} by some cells referencing, such as (A1:A70), (B2:B14), what makes easier to update the formula when needed.
    It’s not an answer to the challenge, but a tip kind of topic related that I would like to share with the Chandoo community.

  85. Iqbal Khan says:

    In the attached sample data, the quality of Audit is taken as above 96% whereas it should be greater than equal to 96%.

    My version of Formula:

    =IF(C4 =1,1000)+IF(D4> = 98%,1000,IF(D4 > = 96%,500,0))

  86. Iqbal Khan says:

    In the attached sample data, the quality of Audit is taken as above 96% whereas it should be greater than equal to 96%.

    My version of Formula:

    =IF(C4 < = 0%,1500,IF(C4 < 3%,1000,0))+IF(F4=0,IF(E4 < =500,1000,IF(F4 = 0,IF(E4 < =560,1000,0))))+IF(G4 > =1,1000)+IF(D4> = 98%,1000,IF(D4 > = 96%,500,0))

  87. Rupert Jones says:

    My try.

    =((C4<3%)+(D4>=98%)+OR(N(E4)<500,N(F4)<560)+N(G4))*1000+((C4=0%)+(D4>=96%))*500

  88. [...] dimineata am dat peste ultima provocare care a postat-o Chandoo la el pe blog, IF Formula Challenge. Desi initial prea avem chef de asa ceva, primul paragraf din articol m-a [...]

  89. Luis says:

    Can this problem be solved with sumproduct?

Leave a Reply