Solved: Calculating weighted average using macro

[Anomandaris]

Hi guys,

I need to show the weighted average at the end of every section, i'm not sure how to get that in a macro, as the quantity varies (number of cells with relevant values, you'll see what i mean on the file).

On the sheet 'SCORE' you can see the raw data
On the Sheet1 you see a table, i dont need any macro for the table, i just want something so i can plug in the weighted average in Column F. In COlumn D you see the Price/unit, in Column E the quantity/lots @ that price....so at the end of each section where it says 'Lots@Avg.Price' I need to have the weighted avg calculated on the same row in Column F.....right now you will see '0's where i need the average........

Any idea how i can do this? Any help would be fantastic

Thanks a lot

[Bob Phillips]

How do you get the average price as price * total, that is total price, a completely different concept.

You can get the average price per group with this array formula in F5 and copied down

=IF(ISNUMBER(D5),"",
SUMPRODUCT(INDEX(D:D,MAX(IF(NOT(ISNUMBER($D$4:D4)),ROW($D$4:D4)))+1):D4,IND EX(E:E,MAX(IF(NOT(ISNUMBER($D$4:D4)),ROW($D$4:D4)))+1):E4)
/SUM(INDEX(E:E,MAX(IF(NOT(ISNUMBER($D$4:D4)),ROW($D$4:D4)))+1):E4))

[Anomandaris]

oh ya the P*Total was another way i was trying to get to the weighted avg.......but you got the right idea

The weighted average its calculating is wrong though
I'm not sure why, but it only calculates it rightly if the quantity is 1.

[Bob Phillips]

Di you array enter the formula?

[Anomandaris]

array enter? how does that work?
I just entered it the way you said up there, just copied and pasted the baove formula on F5 then dragged it down

[Anomandaris]

Oh i just figured out from the web, I did the array now with Ctrl Shift and Enter...........it works great now thanks man!! Awesome