IF Statement Issues

[pedrods]

Hi Guys,

Sorry, im a bit of a newby when it comes to IF statements.

What I am trying to achieve is I want to calculate a drivers pay based on how many deliveries he does with conditions. Initially i thought the IF statement could work but I have 3 constraints and I dont think IF can support 3.

Let "deliveries" be "D"

IF D <=40 then D * 1.20
IF D >=41 but <=50 then D * 1.50
IF D >=51 then D * 1.80

So basically if a driver completes up to 40 deliveries, he receives a rate of 1.20, if he then goes over this lowest standard, he goes on to a 1.50 rate, if he exceeds and goes over 50 deliveries, he then receives 1.80 rate per delivery.

Can this be done?

Please help :-)

[Jonmo1]

Try

=LOOKUP(D1,{0,41,51},{1.2,1.5,1.8})*D1

[Xandler]

This is a bit messy and can probably be shortened but assuming the data starts in D1, it would be like so:

=IF(D1<=40,D1*1.2,IF(AND(D1>=41,D1<=50),D1*1.5,IF(D1>=51,D1*1.8,"")))

[pedrods]

Thanks guys, tried the LOOKUP()*D1, seems to work like a charm.

Thank you!

Pedro

[Jonmo1]

You're welcome.