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.
159 Responses to “An IF Formula Challenge for you”
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)
Hi Jamie,
I think you miss the 5000 overall condition at the end if all A, B,C and D conditions are fulfilled.
Regards.
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.
Although I'm also having a problem with a missing part where it's being parsed as html.
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.
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
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))
In chandoo Solution the condition taken for quality is only >96% actually it should be Quality>=96%
Thanks
Dinesh
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.
here is my solution ... maybe not the shortest one 😀
=IF(C4=0;1500;IF(C4=1;1000;0)+IF(D4>=98%;1500;IF(D4
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...
Love the use of (C4
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.
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)))
`
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 ?
=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)))
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)
=IF(C496%,500)+IF(D4>98%,1000)+IF(OR(E4=1,1000)
For some reason the whole formula is not showing.
=IF(C496%,500)+IF(D4>98%,1000)
+IF(OR(E4=1,1000)
'=IF(C496%,500)+IF(D4>98%,1000)+IF(OR(E4=1,1000)'
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
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
anyone know how to post so that the whole formula will show up?
You should do more challenges like this. I really like them, really tests my skills outside of my profession.
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
trying in a different format:
=(C4=96%)*500
+(D4>=98%)*1000
+((E40)*1000
+(G4>0)*1000
Didn't work. =(
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
Hi! I'm just wondering why do the multipliers are like these
=(C4=96%)*500+(D4>=98%)*1000+((E4<500)+(F40)*1000+ISNUMBER(G4)*1000
*500 when it should be 1500, still your equation is right
so just to try i tried to replace it with 1500 instead i got true as result
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.
Hi Matt,
you're right, it the reason why I added the AND(G4<>''''; G4 >= 1) in my IF
Regards.
>> To all other use < and > instead of < and > in your formulae
Can you explain in words what the <>"" is doing?
@Chelsey
<> is saying Not Equal To
Eg: if(a1<>a2, b1, b2)
If a1 is not equal to a2 then b1 else b2
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.....
totally agree.....0.1/10
*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.
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)
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
Got it down to 113 characters.
=100*((C4<=0.03)*10
+(C4=0)*5
+(D4>=0.96)*5
+(D4>=0.98)*10
+OR(AND(E4 <500,E4<gt;0),AND(F4<560,F4>0))*10
+(G4>=1)*10)
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)
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)
=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)
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.....
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 !!!
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...
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
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
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.
opps: first line should only have one LT.
=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))
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))
=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))
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!
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
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)))
Actually even column E has to be checked for blank cells to be 100% foolproof.
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?
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
=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))
=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))
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"
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)
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,
Oops: remove the LT for the C4 operator and it is there 😉
That was supposed to by = not LT to be removed from the C4... 😉
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...
do I get the job?! 🙂
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.
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
=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))
=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.
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:
Couldn't agree more.
Clarity > Column Compression
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.
=SUMPRODUCT((A2<=C4:C5)*D4:D5+(B2<C7:C8)*(C2=E7:E8)*D7:D8+(D2>0)*D10:D11+(E2>=C13:C14)*D13:D14)
=(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
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))
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)
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))
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
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)
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)
=+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))
=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))
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))
I was bit confused with with Handeling Time condition.
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...!
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
=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))
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))
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
* 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
Oli
This is remarkably similar to my solution, which I haven't posted.
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...
Lovely Concise Version Oli !!!
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
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
=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))
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
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
= 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..!
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
Yes, because you also get 1,000 for being less than 0.03
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
=(
(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))))
)
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
@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.
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.
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)
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)
BTW, if you try to replace 0.5 to .5, excel will replace it to 0.5 again...
@ Jamie,
75 Chr.
=(
((C4 < = 3%)+
((C4 = 0)+(D4 > = 96%))*0.5
+(D4 > = 98%))
+OR(E4 < 500,F4 < 560)
+(G4 > 0)
)*1000
Regards
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
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))
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
Nice one Luke, May the force be with you!
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.
@Luke, That's a good one.
Just adjust your formula to C4 < = 3% and G4 > 0
to get the correct results.
Regards
@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.
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.
@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
=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)
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
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.
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))
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.
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
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))
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.
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.
@Mauricio, try this.
IF(OR(cell_tested={1; 2; 3});TRUE;FALSE).
Regards
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))
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))
My try.
=((C4<3%)+(D4>=98%)+OR(N(E4)<500,N(F4)<560)+N(G4))*1000+((C4=0%)+(D4>=96%))*500
[...] 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 [...]
Can this problem be solved with sumproduct?
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))
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))
So weird, this keeps cutting out part of my option B and C.
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.
"=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))"
This gives me correct answer
"=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))"
This gives me correct answer
"=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)"
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)))
[…] 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 […]
=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")))))
=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"))))))))
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"))))))))
=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))
=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)
=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)
=IF(C4=0,1500,IF(C4<3%,1000,0))+IF((OR(E4<500,F4=1,1000,0)+IF((AND(D4>97.99%,D495.99%,D4<98%))=TRUE,500,0))
if(E5=F5,"1","0"),E5 is TRUE or False and F5 is False how to solve this in excel
=IF(AND(D4 > 98%,100%),1500,IF(AND(D4 > 96%,97,99%),500,0)+IF(G4=1,1000,0)+IF(OR(E4 < 500,F4 < 560),1000,1000)+IF(OR(C4 < 3%,C4=0%),1500,1000))
Is it possible to solve this problem with the IFS formula?
Here is my formula:
=IF(C13>3%,0,IF(C13=0%,1500,1000))+IF(OR(E13<500,F13=1,1000,0)+IF(D13>=98%,1500,IF(D13>=96%,500,0))