Re: Perform multiple calculations when a value goes over a threashold

i think this formula should do what you are after - do a little bit of testing

=IF(SUM(K$3:K4)<=10000,K4*H$3,IF(SUM(K$3:K3)>10000,K4*I$3,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))

in L3 the 1st entry just use miles*rate =K3*H3

then in L4 - put the formula and copy down

**=IF(SUM(K$3:K4)<=10000,K4*H$3**,IF(SUM(K$3:K3)>10000,K4*I$3,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))

the 1st section is testing to see if the total mileage including this periods is less than or equal to 10000 - if so then use the H3 rate

=IF(SUM(K$3:K4)<=10000,K4*H$3,**IF(SUM(K$3:K3)>10000,K4*I$3**,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))

the 2nd section is testing is the mileage upto last period more than 10000 - if it is then this period will be more than 10000 so use the I3 rate

=IF(SUM(K$3:K4)<=10000,K4*H$3,IF(SUM(K$3:K3)>10000,K4*I$3,**((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3**))

the 3rd section must therefore be across 10000 and the mileage should be at the two different rates

**((10000-SUM(K$3:K3))*H$3)**

takes 10000 away from the mileage upto last period - and so works out how many miles this period are at the < 10000 rate

**(K4-(10000-SUM(K$3:K3)))**

takes the miles for this period and takes away from the 10000 - the mileage upto last period to work out the miles over 10000

I have played with your sample - and just added miles - so column K does not have your formula in all cells