Formula Help!

[Becks]

I have a workbook with 12 sheets, each sheet represents a different month
where i record my mileage each day. Each sheet is set out as follows:

Date Miles Cost
01/04/06 800 £320.00

02/04/06 100 £40.00
03/04/06 150 £52.50
04/04/06 100 £25.00

I can claim 40p per mile for the first 10,000 miles, then it's 25p per mile
after that. I need a formula that will work out the amount i can claim for
each days travel based on the rates mentioned.

Can anyone help, i've been at this for ages and can't come up with anything
from my very limited knowledge???

 

[john]

do you mean 40p for the first 100 miles not 10000 miles? if so try
=IF(A1>100,(100*B1)+C1*(A1-100),B1*A1) with the miles in A1, 40p in B1 and
25p in C1
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Teethless mama]

=IF(B2<=10000,B2*0.4,10000*0.4+(B2-10000)*0.25)

 

[Becks]

I didn't explain that very well, sorry! Each sheet is as follows:

A B
C
1 Date Miles Cost
2 01/04/06 800 £320.00
3 02/04/06 100 £40.00
4 03/04/06 150 £52.50
5 04/04/06 100 £25.00

The rates are 40p up to 1000 miles and 25p for all miles after that. C2 is
800 * 40p, C3 is 100 * 40p, C4 puts me over the thousand, so it would be 100
* 40p and 50 * 25p. The miles travelled for the remainder of the year would
be at 25p, as the thousand miles mark has been passed.

I want to be able to just enter the date and amount of miles each day and
the formula in column c to work out the amount to claim. The formula will
need to look at all miles travelled in previous days/months then revert to
25p once the limit has been reached. Hope this makes more sense?

 

[Becks]

I've tried to explain it better on my second post, the rates aren't for each
day it is for the year. So the first 1000 miles in the year i get 40p per
mile then the rest are at 25p.

 

[Teethless mama]

=IF(SUM(B2:B100)<=10000,SUM(B2:B100)*0.4,10000*0.4+(SUM(B2:B100)-10000)*0.25)

 

[Becks]

I think that would give me my cost for a particular month, but i need a
seperate calculation for each day. Then when a new month begins i need it
to do a calculation for each day of that month but also taking in to account
the miles done in previous months?

 

[anthon]

f your figure of 10,000 is right, I imagine it refers to cumulative miles,
not to the single day's travel in isolation, which is what John's formula
deals with. So you need frst to add up cumuluative miles, say in column C
(add today's mles to yesterday's cumulative total from the cell above) and
then in the next column work out the day's reimbursement with the following
(where A20 is today's date, B20 is today's milage,and C20 is the cumulative
milage to date):
=IF(C20<10000;C20*0,4;4000)+(SE(C20<10000;0;(C20-10000)*0,25))-(SOMMA(D$1419))
Don't use this formula in the first row of the table (it will be self
referential), but simply calculate the first day's miles at 40 pence a mile.

 

[Sandy Mann]

Will

=IF(SUM($B$2:B2)<1000,B2*0.4,IF(AND(SUM(B1:B2)>1000,SUM($B$1:B1)<1000),(SUM($B$2:B2)-1000)*0.25+(1000-SUM($B$1:B1)*0.4),B2*0.25))

Return what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk