# An IF Formula Challenge for you

Posted on April 25th, 2012 in Excel Challenges , Excel Howtos - 152 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.

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.

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

Thanks to Galo for emailing this question.

 Lets meet when I am in Australia Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4]
 Written by Chandoo Tags: downloads, Excel 101, homework, if() excel formula, Learn Excel, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 152 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:

=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 😀

=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..

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)

• Peter says:

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

• Peter says:

For some reason the whole formula is not showing.

=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

• Greg says:

anyone know how to post so that the whole formula will show up?

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

• Luke M says:

trying in a different format:
=(C4=96%)*500
+(D4>=98%)*1000
+((E40)*1000
+(G4>0)*1000

• Luke M says:

Didn't work. =(

• Luke M says:

Thanks Chandoo for the new posting options!

Complete formula, corrected for the col G not being truly blank problem:
=(C4<3%)*1000+(C4=0)*500+(D4>=96%)*500+(D4>=98%)*1000+((E4<500)+(F4<560)>0)*1000+ISNUMBER(G4)*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.

• Cyril Z. says:

Hi Matt,

you're right, it the reason why I added the AND(G4<>''''; G4 >= 1) in my IF

Regards.

>> To all other use &lt; and &gt; instead of < and > in your formulae

• Chelsey M says:

Can you explain in words what the <>"" is doing?

• Hui... says:

@Chelsey
<> is saying Not Equal To
Eg: if(a1<>a2, b1, b2)
If a1 is not equal to a2 then b1 else b2

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.....

• manju says:

totally agree.....0.1/10

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)

• Jamie Bull says:

107 characters:

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

• Steven Gates says:

103 characters:

=100*((C4<=3%)*10+(C4=0)*5
+(D4>=96%)*5+(D4>=98%)*10
+OR((E4<500)*(E4>0),(F4<560)*(F4>0))*10
+(G4>=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.

• Ben Niebuhr says:

opps: first line should only have one LT.

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)))

• Mayank Bhatia says:

Actually even column E has to be checked for blank cells to be 100% foolproof.

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,

• Michael says:

Oops: remove the LT for the C4 operator and it is there 😉

• Michael says:

That was supposed to by = not LT to be removed from the C4... 😉

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:

• Jon Pynn says:

Couldn't agree more.

Clarity > Column Compression

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

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))

• Anni says:

I was bit confused with with Handeling Time condition.

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

• Hui... says:

Oli
This is remarkably similar to my solution, which I haven't posted.

• Oli says:

Cool - looks like I am finally getting better 🙂

I have also noticed that Chandoo's forumal IS correct.  The file was playing up and re-typing the commendation values corrected the issue...

• Rohit1409 says:

Lovely Concise Version Oli !!!

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

• Robert Clark says:

Yes, because you also get 1,000 for being less than 0.03

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:

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

• cALi says:

Nice one Luke, May the force be with you!

• Jamie Bull says:

Got some good ideas in there, I like! They all come back as true where either the voice or fax handling time is blank though.

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.

• Elias says:

@Mauricio, try this.

IF(OR(cell_tested={1; 2; 3});TRUE;FALSE).

Regards

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?

90. Tom Jefferson says:

If the 4th condition uses the AND function, why is the AND function not used in the 1st function? The two conditions are basically the same structure.
I believe the correct answer for the 4th condition could also be calculated as:
=IF(D13>97.99%,1500,IF(D13>95.99%,500,0))

91. I broke the formula down by its subsections and pasted below.

A. IF(C4=0,1500,IF(C4 < 3%,1000,0))
B. IF(OR(E4 < 500,F4 = 1,1000,0)
D. IF(D4 > 98%,1500,IF(AND(D4 96%),500,0))

• For some reason my comment didn't post correctly. I broke the formula down by its subsections and pasted below.

A. IF(C4=0,1500,IF(C4 < 3%,1000,0))
B. IF(OR(F4 < 500,G4 = 1,1000,0)
D. IF(D4 > 98%,1500,IF(AND(D4 > =96%,D4 < = 97.99%),500,0))

92. Swalih says:

Hi Chandoo,

It says in the conditions
Note: They can never have both phone and fax request

Still EMP0003 to EMP0010 have both Voice time & Fax time.

93. Swalih says:

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

94. Swalih says:

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

95. Brian Skinnell says:

"=IF(C12<0.03,1000)+IF(C12=0,500,0)+IF(OR(AND(E12"",E12<500),AND(F12"",F120,1000,0)+IF(AND(D12=0.98),1500,0)+IF(AND(D12=0.96),500,0)"

96. Warren Silva says:

I used:
SUM(IF(C4=0,1500,IF(C4 < 0.03,1000,0)),IF(OR(E4 < 500,F4 = 0.98,1500,IF(D4 > = 0.96,500,0)))

97. […] Excel bloggers, included the IF function in his list of top 10 formulas for aspiring analysts and stated that “if you are able to write IF formulas for any situation, then you are bound to be awesome in […]

98. achyutanam says:

=IF(C4=0%,"1500",IF(G4>1,"1000",IF(OR(D4>98%,D496%,D4<7.99),"500",IF(OR(D4<3%,E4<500,F4<560)," 1000")))))

99. achyutanam says:

=IF(C4=0%,"Php1500",IF(C4<=3%,"Php1000",IF(E4<500,"php1000",IF(F41,"Php1000",IF(AND(D4>98%,D4=96%,D4<97.99%),"php500",IF(OR(C4<3%,E4<500,F4=1,D4>96%,D4<100%),"PHP5000"))))))))

100. achyutanam says:

excel challenge

=IF(C4=0%,"Php1500",IF(C4<=3%,"Php1000",IF(E4<500,"php1000",IF(F41,"Php1000",IF(AND(D4>98%,D4=96%,D4<97.99%),"php500",IF(OR(C4<3%,E4<500,F4=1,D4>96%,D4<100%),"PHP5000"))))))))

101. Ian says:

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

102. Dilbert says:

=IF(C4=0%,500,0)+IF(C4 < 3%,1000,0)+IF(E4 < 500*(ORF4 0,1000,0)+IF(D4 > =98%,1000,0)+IF(D4 > =96%,500,0)

103. anuj says:

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

 Lets meet when I am in Australia Adding Macros & Final Touches to Customer Service Dashboard [Part 4 of 4]