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.

 

[jasonb75]

The only thing to watch out for with the way that I've written the formula is that it has no upper limit, so in theory using a relatively unlikely example, 1000 appearances would equate to basic * 6

If you need to cap it, then it would mean including the MIN function in the formula, i.e.

=N5*O5*($O$3*(1+FLOOR(MIN(P5,100)/200,0.05)))

Which sets the cap at 100 appearances, or

=N5*O5*($O$3*MIN(1+FLOOR(P5/200,0.05),2))

which sets the cap at basic * 2

 

[Affo01]

That makes perfect sense, thank you.

 

[jasonb75]

You're welcome, thanks for the feedback