formula for mileage sheet

[bricktop]

hello i am new to this so plz be patient with me.
i am trying to make a spread sheet for my travel allowance for my
buisness but i cant seem to work out a formula to change the percentage
amount when i get to 10,000 miles. My sheet is laid out with a date on
the left column with one column to the right with the weekly amount of
miles traveled which are summed to total. Then i need to multiply this
total by 40% to get my allowance. That bit is fine but then the tricky
part is after 10,000 miles i have to change the percentage rate from
40% to 25% because i am only allowed the higher rate for that amount
and anything thereafter has to be the lower rate. Any help would be
appreciated thx. i have attached the file for viewing with what i have
been trying to do but as u can see ive failed totally and it has got me
going round in circles.

 

[Norman Harker]

Hi bricktop!

Use something like:

Assuming first week mileage is in B1 and subsequent week's mileage
underneath:

=IF(SUM($B$1:B1)>1000,B1*40%,B1*25%)
copy down

To avoid the calculation for unentered weeks use:
=IF(B1="","",IF(SUM($B$1:B1)>1000,B1*40%,B1*25%))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Sandy Mann]

With the total in B100 try:

=B100*25%+MIN(B100,10000)*15%

HTH

Sandy