Can I have a function in Excel that changes a sum once the value of a certain column exceeds a speci-CodePudding

For my accounts, I can claim certain amounts of mileage back against tax: £0.45/mile up to 10,000 miles £0.25/mile after 10,000 miles

In my spreadsheet, I have a column for miles covered per job (I). To work out the flat rate travel cost (column J) I multiply the cell in I by 0.45. (=SUM(I2*0.45))

Can I have a formula where the cell in I is multiplied by 0.25 after the total value of I exceeds 10,000?

I don't know whether this is possible/practical

CodePudding user response：

Sure you can:

=IF(SUM(I:I*0.45)>10000,SUM(I:I*0.25),SUM(I:I*0.45))

if your data starts from I3 till the bottom and your calculation is on I2 then:

=IF(SUM(I3:I10000*0.45)>10000,SUM(I3:I10000*0.25),SUM(I3:I10000*0.45))

CodePudding user response：

There are two ways to approach this task. Option 1 is to use a running total, and when that running total crosses over the 10,000 threshold, you use a blended rate which is equivalent of 0.45 for the portion still within the 10K limit 0.25 for the portion over the 10K limit. All subsequent jobs get the 0.5 rate applied to them.

See concepts below.

In order to do this I used a helper column to hold the running total. I placed that in column L since you said you wanted the cost in column J. Personally I would prefer the cost to be at the far right with the helper columns in between.

In L5 place the following formula:

=I5

In L6 place the following formula and copy down as needed:

=I6 L5

In K5 use the following formula:

=IF(L5<=10000,0.45,IF(L5>10000,10000*0.45 (L5-10000)*0.25))

In K6 use the following formula and copy down:

=IF(L6<=10000,0.45, IF(AND(L5<10000,L6>10000),((10000-L5)/I6)*0.45 ((L6-10000)/I6)*0.25,0.25))

In J5 place the following formula and copy down:

=K5*I5

Option 2 is to proportion the 10,000 miles at the higher rate bases on how many miles were driven for that job. While this sounds more complicated, it is more fare for what each job gets billed. Note in the example below, that the inputs and totals are the same, but the amount each job is billed between option 1 and option 2 is different.

For option 2 to work, I made to working columns where I broke the portion of low miles and high miles for each job. In order to determine what portion of the low miles a job would get it was simply:

miles for job / all miles driven * 10000

So in K14 use the following and copy down to determine the portion of the low mileage for the job:

=I14/SUM($I$14:$I$18)*10000

In L14 us the following and copy down to determine the portion of the high mileage for the job:

=I14-K14

Then place your low and high mileage rate in a cell somewhere. I chose cells M14 and N14 respectively.

To determine your cost for a job, multiply your low mileage by low mileage rate and similar for high mileage. In cell J14 I used the following formula and copied down:

=K14*$M$14 L14*$N$14

On a side note, I would highly recommend placing your rates and your 10K threshold amount in a cell and referring to those cells with your formula instead of hard coding. That way if there rates change or the threshold changes, you just need to change the value in a cell instead of editing a whole bunch of formulas.

Below is my preferred layout:

If you want to avoid the helper columns you can but back substituting the reference cells into the final cost formula.

When you do, you can get something that looks like this:

Note the formulas in J2, J3 copied down, and J14 copied down are as follows:

J5
=IF(SUM($I$5:I5)<=$N$4,$O$4,IF(SUM($I$5:I5)>$N$4,$N$4*$O$4 (SUM($I$5:I5)-$N$4)*$P$4))*I5

J6
=IF(SUM($I$5:I6)<=$N$4,$O$4, IF(AND(SUM($I$5:I5)<$N$4,SUM($I$5:I6)>$N$4),(($N$4-SUM($I$5:I5))/I6)*$O$4 ((SUM($I$5:I6)-$N$4)/I6)*$P$4,$P$4))*I6

J14
=I14/SUM($I$14:$I$18)*$N$4*$O$4 (I14-I14/SUM($I$14:$I$18)*$N$4)*$P$4