Custom weighted average

[khsehmi]

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.

 

[Takae]

Array formula(Ctrl+Shift+Enter)
={SUM(A2:A4*B2:B4)/SUM(A2:A4)}

Curly braces will show automatically.