IFs and AND help please

[Affo01]

Hello all.

I'm trying to work out a way to automate some wages. It is for a sports team and I want to include some sort of bonuses for a) winning or drawing and keeping a clean sheet and b) loyalty, the more games you play, the more you earn (base * multiplier).

The screen shot shows the formula I've managed to create and works fine while less than 9 appearances (column P keeps a count). Column O shows 1 for starting and 0.5 for being a substitute. Upto and incl 9 appearances you get your basic wage (O3) multiplied by the result factor (columns L:N), but when you hit 10-19 appearance you get your basic * result outcome *1.05 and here's where I've got an error and getting a FALSE reply. Cell Q14 should return ((40*1.05)*1.05)=44.10. I know it is something obvious, but I can't work it out.

Please can someone take a look and point me in the correct direction or ask for more info if you need it.

 

[rondeondo]

Hi Affo
It's a long complex formula, getting a false suggests you have missed putting in one of the result options for one of the levels of ifs... ie if(exp,true,false)
I usually build long complex things like this across multiple columns til I know I have the right result then put it all together in one column if necessary.
To fault find though excel has "Evaluate formula" under the formulas tab. give this a run and it may help find where you've missed an option.

 

[jasonb75]

It would be easier for people to help you if you used xl2bb to post your examples instead of a screen capture. A screen capture can not be edited in excel without retyping everything (very time consuming).

 

[Toadstool]

Hi Affo01,

It's that first "IF(P14<10" because row 14 and below all have Pnn =>10 and it looks like you've satisfied the "if true" part but have no "if false" part so it drops into one of those closing brackets at the end and gives FALSE.

 

[Affo01]

Wages 2021 idea2.xlsx
	N	O	P	Q
3		40
4
5	1.1	1	1	44
Sheet1

Cell Formulas
Range	Formula
N5	N5	=L5+M5-1
P5	P5	=COUNT(O5)
Q5	Q5	=IF(P5<10,IF(O5=1,O$3*N5,IF(O5=0.5,(O$3*N5)/2,IF(AND(P5<21,P5>9),IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.05,IF(AND(P5>20,P5<31),IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.1,IF(AND(P5>30,P5<41),IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.15,IF(P5>40,IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.2,"")))))))))))))))

Have I managed to do that right from xl2bb?

The evaluate formulae didn't help much.

 

[Affo01]

This might help more, and thank you for your insights so far.

Wages 2021 idea2.xlsx
	N	O	P	Q
3		40
4
5	1.1	1	1	44
6	1.1	1	2	44
7	1.15	1	3	46
8	1.1	1	4	44
9	1	1	5	40
10	1	1	6	40
11	1.1	1	7	44
12	1.1	1	8	44
13	1.15	1	9	46
14	1.05	1	10	FALSE
15	1		10	FALSE
16	1		10	FALSE
17	1	1	11	FALSE
18	1.1	1	12	FALSE
19	1	0.5	13	FALSE
20	1.1	1	14	FALSE
21	1		14	FALSE
22	1.05	1	15	FALSE
Sheet1

Cell Formulas
Range	Formula
P5	P5	=COUNT(O5)
Q5:Q13,Q16:Q22	Q5	=IF(P5<10,IF(O5=1,O$3*N5,IF(O5=0.5,(O$3*N5)/2,IF(AND(P5<21,P5>9),IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.05,IF(AND(P5>20,P5<31),IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.1,IF(AND(P5>30,P5<41),IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.15,IF(P5>40,IF(O5=1,O$3*N5,IF(O5=0.5,((O$3*N5)/2)*1.2,"")))))))))))))))
P6:P22	P6	=COUNT(O$5:O6)
Q14:Q15	Q14	=IF(P14<10,IF(O14=1,O$3*N14,IF(O14=0.5,(O$3*N14)/2,IF(AND(P14>9,P14<21),IF(O14=1,O$3*N14,IF(O14=0.5,((O$3*N14)/2)*1.05,IF(AND(P14>20,P14<31),IF(O14=1,O$3*N14,IF(O14=0.5,((O$3*N14)/2)*1.1,IF(AND(P14>30,P14<41),IF(O14=1,O$3*N14,IF(O14=0.5,((O$3*N14)/2)*1.15,IF(P14>40,IF(O14=1,O$3*N14,IF(O14=0.5,((O$3*N14)/2)*1.2,"")))))))))))))))
N5:N22	N5	=L5+M5-1

 

[jasonb75]

Are these results correct?

edit:- revised formula, I only noticed the additional tiers beyond 1.05 after posting.

Sample Data.xlsx
	N	O	P	Q
3		40
4
5	1.1	1	1	44.00
6	1.1	1	2	44.00
7	1.15	1	3	46.00
8	1.1	1	4	44.00
9	1	1	5	40.00
10	1	1	6	40.00
11	1.1	1	7	44.00
12	1.1	1	8	44.00
13	1.15	1	9	46.00
14	1.05	1	10	44.10
15	1		10	0.00
16	1		10	0.00
17	1	1	11	42.00
18	1.1	1	12	46.20
19	1	0.5	13	21.00
20	1.1	1	14	46.20
21	1		14	0.00
22	1.05	1	15	44.10
Sheet8

Cell Formulas
Range	Formula
Q5:Q22	Q5	=N5*O5*($O$3*(1+FLOOR(P5/200,0.05)))

 

[Affo01]

Yes, that looks correct so far, but when they get to 20 appearances in column P, the multiplier needs to be 1.1 and if they hit 30, 1.15 and for 40 and above 1.2 if that makes sense? I fear that is where my initial attempt failed with all the IFs and ANDs.

But thank you so far, I can see how you simplified the first part at least.

 

[jasonb75]

I think that you might have missed the edit that I made, I noticed that after posting and changed the formula in my reply above.

 

[Affo01]

Yes, sorry I did miss your edit, must have been typing my reply, but that looks absolutely perfect. Never knew about FLOOR! I like learning new things and will have to look more into how that works.

Thank you so much.