ProRata formula for SUM

[sgm1945]

I've got a list of data in column E and F.

If I want to find the value of 5,000,000 units, I would take (starting from the top row) the sum of the values of cells F4-F7, and that would value 4,500,000 units. To get the remaining value of another 500,000 units, I would pro rata F8, by taking (500000/E8)*222.

How would I do this using a FORMULA or macro? As in, I type the value I want to prorata in cell I4, and the value will automatically come out in cell J4.

 

[MarcelBeug]

Formula solution: first define name IndexCumulativeFound via tab FORMULAS - Name Manager with formula:

=MATCH($I$4,SUBTOTAL(9,INDIRECT(TRANSPOSE("$E$4:$E$"&ROW($E$4:$E$9)))))

This will return the index of the relative row where the cumulative value is found (approximate match), i.c. 4.

Formula in J4:

=SUM($F$4:INDEX($F$4:$F$9,IndexCumulativeFound))+(I4-SUM($E$4:INDEX($E$4:$E$9,IndexCumulativeFound)))/INDEX($E$4:$E$9,1+IndexCumulativeFound)*INDEX($F$4:$F$9,1+IndexCumulativeFound)

 

[StephenCrump]

Another approach that works well with variable scales like this (e.g. tax rates, commissions) is to structure your table like this:

C2: =B3/(A3-A2), copy down
D3: =B3/(A3-A2), copy down
Threshold: A2:A8
IncrementalRate: D2:D8

B12: =SUMPRODUCT(--(B11>Threshold),B11-Threshold,IncrementalRate)

I don't know what you want to do beyond 10,500,000? For the moment, I've set the marginal rate to Nil.

Excel 2010

	A	B	C	D
Incremental Rate
Target
Value

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Threshold[/TD]
[TD="align: right"]Total [/TD]
[TD="align: right"]Marginal Rate[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$0.000123[/TD]
[TD="align: right"]$0.000123[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1,000,000[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]$0.000456[/TD]
[TD="align: right"]$0.000333[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2,000,000[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]$0.001578[/TD]
[TD="align: right"]$0.001122[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2,500,000[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]$0.000056[/TD]
[TD="align: right"]-$0.001523[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4,500,000[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]$0.000056[/TD]
[TD="align: right"]$0.000000[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8,500,000[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]$0.000167[/TD]
[TD="align: right"]$0.000111[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]10,500,000[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]$0.000000[/TD]
[TD="align: right"]-$0.000167[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]5,000,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1,506.75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[Marcelo Branco]

Or


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Units​

[/TD]
[TD]

Value​

[/TD]
[TD]

Helper Cell​

[/TD]
[TD][/TD]
[TD]

Target​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1000000​

[/TD]
[TD]

123​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

5000000​

[/TD]
[TD]

1506,75​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1000000​

[/TD]
[TD]

456​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

500000​

[/TD]
[TD]

789​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

2000000​

[/TD]
[TD]

111​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

4000000​

[/TD]
[TD]

222​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

2000000​

[/TD]
[TD]

333​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in G4 (Helper Cell)
=IFERROR(MATCH(I4,SUBTOTAL(9,OFFSET(E4:E9,,,ROW(E4:E9)-ROW(E4)+1))),"")
confirmed with Ctrl+Shift+Enter

Formula in J4
=IF(G4="","",IF(G4=6,SUM(F4:F9),SUM(F4:INDEX(F4:F9,G4))+(I4-SUM(E4:INDEX(E4:E9,G4)))/INDEX(E4:E9,G4+1)*INDEX(F4:F9,G4+1)))

Hope this helps

M.