Subtracting based on number of miles

[Guest]

Okay working on a timesheet--still--

I need to be able to take the total number of miles driven and subtract a
fixed number based on the total number of miles driven.

For 1-99 miles subtract 50 miles--so for 91 miles driven I need it to show
as 41 miles
For 100-199 subtract 25 miles--so for 175 miles driven I need it to show as
150 miles

Over 200 Subtract 0 miles-- so 225 as 225

Thanks!
--Ms Chewie

 

[Biff]

Hi!

Here's one way.

E1 = miles driven

=IF(E1="","",E1-LOOKUP(E1,{0,0;1,50;100,25;200,0}))

Biff

 

[David McRitchie]

Does that mean that if you drive only 25 miles you would
reimburse the company based on negative miles instead of
getting paid for one file if you drove 51 miles.

The easiest and most flexible way would be to set up a table for use with
VLOOKUP then you don't have to change formulas..
http://www.mvps.org/dmcritchie/excel/vlookup.htm

You might play with the following formula, it will not assign fewer than zero miles..
=IF(ISNUMBER(A2),IF(A2>=200,A2,IF(A2>=100,A2-25,MAX(0,A2-50))),"")

 

[Guest]

Thanks, This worked great!

Hi!

Here's one way.

E1 = miles driven

=IF(E1="","",E1-LOOKUP(E1,{0,0;1,50;100,25;200,0}))

Biff