How can I do this?

[nd2no]

l2 = # of miles. q4 = bonus.

How can I make q4 show the bonus based on the following:

10,000 miles = $50
15,000 miles = $75
20,000 miles = $10

 

[Peo Sjoblom]

=VLOOKUP(12000,A1:B3,2)

where A1:A3 holds

10000
15000
20000

and B1:B3 holds the amounts

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Sandy Mann]

Or in a formula:

=(A1>=10000)*ROUNDDOWN(MIN(A1,20000)/5,-3)/40

or if 20000 is not a high limit simply

=(A1>=10000)*ROUNDDOWN(A1/5,-3)/40

and if 10000 is not a low limit even simpler:

=ROUNDDOWN(A1/5,-3)/40

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

=VLOOKUP(12000,A1:B3,2)

where A1:A3 holds

10000
15000
20000

and B1:B3 holds the amounts

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Peo Sjoblom]

VLOOKUP is not a formula? If you meant not having to use a table

=VLOOKUP(A1,{0,0;10000,50;15000,75;20000,100},2)


--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Or in a formula:

=(A1>=10000)*ROUNDDOWN(MIN(A1,20000)/5,-3)/40

or if 20000 is not a high limit simply

=(A1>=10000)*ROUNDDOWN(A1/5,-3)/40

and if 10000 is not a low limit even simpler:

=ROUNDDOWN(A1/5,-3)/40

Sandy

 

[CLR]

In cell Q4 put this formula.........

=IF(I2>=20000,"$100",IF(I2>=15000,"$75",IF(I2>=10000,"$50","0")

Vaya con Dios,
Chuck, CABGx3

 

[Sandy Mann]

Sorry Peo, wrong terminology I meant a calculated answer.

Regards

Sandy