Custom weighted average

khsehmi

New Member
Joined
Nov 15, 2015
Messages
1
Hi,

I have the planned numbers in cell A1 to B4.
A1 - Planned Amount
B1 - Planned Rate

A2 - 200
A3 - 1770
A4 - 280

B2 - 92.1175
B3 - 91.9803
B4 - 92.07

Actuals are now from A6 - Actual Amount and B7 - Wtd Rates (These are headers)

in A7 - 1820 (actual total amount)
In B7 I need the weighted average till actual amount (looking for 200 at 92.1175 and then 1620 at 91.9803 which is 91.99538)

The above planned could be more than 3 rows, i.e. distributed in a more fragmented manner.

If anyone can help me with the formula(e) please.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Array formula(Ctrl+Shift+Enter)
={SUM(A2:A4*B2:B4)/SUM(A2:A4)}

Curly braces will show automatically.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top