Help on if statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to develop a two tier 'if' statement for a works bonus scheme and
struggling a bit. What I'm trying to achieve is the following:

If points>60 then return £2.5 for any points over 60. If less then 60
return 0. Then if points are => 150 then return £3.10 for any points over
150. So far I have:

=IF(E80>60,2.5*(E80-60),IF(E80>=150,3.1*(E80-150),0)) but it goes wrong over
150 points.

Can anyone help me correct this.

Thanks
 
If over 150 do you then need to add 90*2.5 as well as 3.1* whats over 150
If so you just need to put 225+ just before your 3.1 in your formula

if I have missed the point then reply and I will have another try!!

Patrick
 
Patrick

You are write - thank you. In the meantime I worked it out myself and ended
up with:

=IF(L32<60,0,IF(L32>=150,0.6*(L32-149)+(2.5*(L32-60)),2.5*(L32-60)))

probably making it more complicated than I need to so I'll just add the £225
onto tjtjjtjt's formula.

Thanks for your help
 
=IF(E80<60,0,IF(E80>=150,3.1*(E80-150),2.5*(E80-60)))
will do what you want, but you may want to look at the VLOOKUP Function in
the Help Files.

tj
 
Thank you for your response, ubt having pasted this in, I don't think I
explained it properly.

If the points are between 60 and 149 than they get £2.50 per point up to 149
points and then once they reach 150 points they get £3.10 per point, plus
the £2.50 per point up to 149 points.

so if they achieve 152 points the answer should be (149-60*2.50)=£222.50+
(152-149*£3.10)= £9.30) total £238 but I'm getting £3.10.

Sorry if I didn't explain it quite right
 
Back
Top