Changing a formula calculation when a total passes a pre-defined value

[davidatgb]

Hello. I need the help of a better brain than mine, please.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Has anyone ever seen or worked out a formula for calculating mileage allowances when the first 10,000 miles in a year is claimed at $0.45 per mile and all mileage over 10,000 is calculated at $0.25 per mile?
<o></o>
I tried this one =IF(C38<10000,(D12*0.45),(D12*0.25)) where: <o></o>
D12 is the cell containing the mileage entered that day.<o></o>
C38 is the sum of all my accumulated mileage that year and is the sum of all the values calculated in column D.
<o></o>
This does not work properly, of course, because as soon as the number in C38 exceeds 10,000, all previous cells as well as all subsequent cells revert to the 0.25 value and I lose my first 10,000 miles using the .45 value.<o></o>
Can anyone suggest a formula that allows the calculation in all cells in column D to be and remain at 0.45 while C38 remains below 10,000, and then switch the calculation to the 0.25 value in subsequent cells only once C38 exceeds 10,000?
<o></o>
Thank you in advance - David.<o></o>

 

[Paul]

Assuming your data starts in d1 you can use this or adjust.

=IF(SUM($D$1:D1)<10000,D1*0.45,D1*0.25)

And of course copy down. Its not perfect but maybe more of what you are looking for.

 

[davidatgb]

Thanks for that, Paul.

I shall give it a try and let you know the outcome.

David.

 

[Weaver]

try:

=MIN(10000,C38)*0.45+MAX(0,C38-10000)*0.25

I think there's a better way still, but this ought to do it.