If formula

[Guest]

I’m trying to update a formula for our employee bonus system, but I’m afraid
I’ve got in a bit of a muddle.
We currently have
=IF(x<60,0,(2.5*(x-60)))

Where x is the number of points they achieve in a week. They get paid £2.50
for every point over 60points.
I want to change it so that up to 150 points they get paid £2.10 for every
point over 60, but it cuts off at 150 points.
Then shown on a separate line (so they can see the distinction) I want to
show £2.15 for every point between 150 & 175 points.
Then again on a separate line £2.20 for any points over 200
I really have no idea how to go about this – I think this forum helped me
set up the original formula 3 years ago!
Can anyone help?
Thanks

 

[Guest]

Try this:
If A is your points column, pase this formula in column B:

=IF(A1<=60,0,IF(AND(A1>60,A1<=150),(2.1*(A1-60)),(2.1*90)))

This will calculate your point bonus between 60 and 150 points.
For the next column, modify the formula so it reads:

=IF(A1<=150,0,IF(AND(A1>150,A1<=175),(2.15*(A1-150)),(2.15*25))

Notice that I've just changed the numbers to reflect the point bonus between
150 and 175 points. Do the same thing for the next colum, Change your
starting and ending values, and the pound bonus.

-- Anne Murray

 

[Guest]

Thank you for your help. It works perfectly for the first column, but when i
put 185 points in to test the second column I get a return of 'false'
instead of 75.25

 

[David Biddulph]

The 2nd formula isn't legal. The parentheses don't match, so you shouldn't
have been able to get an answer. I don't know how you changed it to get an
answer, but it should just have an extra closing parenthesis at the end, so
that the syntax loks the same as the first formula.

 

[Guest]

That's right, I lost the last parenthesis when copy-pasting, my bad.

 

[Guest]

Perfect

thank you very much

Pauline

That's right, I lost the last parenthesis when copy-pasting, my bad.