| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help!
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??? -- Cheers Becks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
do you mean 40p for the first 100 miles not 10000 miles? if so try
=IF(A1100,(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) "Becks" wrote: 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??? -- Cheers Becks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
=IF(B2<=10000,B2*0.4,10000*0.4+(B2-10000)*0.25)
"Becks" wrote: 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??? -- Cheers Becks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
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? -- Cheers Becks "john" wrote: do you mean 40p for the first 100 miles not 10000 miles? if so try =IF(A1100,(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) "Becks" wrote: 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??? -- Cheers Becks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
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. -- Cheers Becks "Teethless mama" wrote: =IF(B2<=10000,B2*0.4,10000*0.4+(B2-10000)*0.25) "Becks" wrote: 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??? -- Cheers Becks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
=IF(SUM(B2:B100)<=10000,SUM(B2:B100)*0.4,10000*0.4 +(SUM(B2:B100)-10000)*0.25)
"Becks" wrote: 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??? -- Cheers Becks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
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? -- Cheers Becks "Teethless mama" wrote: =IF(SUM(B2:B100)<=10000,SUM(B2:B100)*0.4,10000*0.4 +(SUM(B2:B100)-10000)*0.25) "Becks" wrote: 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??? -- Cheers Becks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help!
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$14:D19)) 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. "Becks" wrote: 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??? -- Cheers Becks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
