sum and weight average variable data

[drdrez]

Hi there, been using this for a number of years now, but never had to post a question - I guess most things are already answered - but this one is a bit more tricky.

I want to calculate a totals and a weighted average based on months from a data set that has duplicate ID's and variable data (attached example).

I would like to calculate the total tonnes by month and the average grade of those tonnes. Using the 'month ID table' as a reference to the 'Raw Data table'.

Is this possible using if and vlookup functions or will require a higher level of programming knowledge??

If you can please help

[NBVC]

Try these formulas:

I2:

=SUM(IF(ISNUMBER(MATCH($A$3:$A$25,$E$3:$E$12,0)),$B$3:$B$25))

J2:

=AVERAGE(IF(ISNUMBER(MATCH($A$3:$A$25,$E$3:$E$12,0)),$C$3:$C$25))

I3:

=SUM(IF(ISNUMBER(MATCH($A$3:$A$25,$F$3:$F$12,0)),$B$3:$B$25))

J3:

=AVERAGE(IF(ISNUMBER(MATCH($A$3:$A$25,$F$3:$F$12,0)),$C$3:$C$25))

each of these confirmed with CTRL+SHIFT+ENTER not just ENTER.

Note: If you transpose the column/row headers in your summary, then it would be easier to enter the formulas once and copy them across the columns.

[drdrez]

Works perfectly - Ill try and relate it to my actual data set and let you know how I get on

Much appreciated

[drdrez]

Actually how would I make that average a weighted average for the corresponding tonnes?

[NBVC]

you mean?

=sum(if(isnumber(match($a$3:$a$25,$e$3:$e$12,0)),$b$3:$b$25*$c$3:$c$25))/sum(if(isnumber(match($a$3:$a$25,$e$3:$e$12,0)),$c$3:$c$25))

confirmed with CTRL+SHIFT+ENTER

[drdrez]

Perfect - I just tweaked it to be a weighted average of grade by tonnes rather that the other way around:
=SUM(IF(ISNUMBER(MATCH($A$3:$A$25,$E$3:$E$12,0)),$B$3:$B$25*$C$3:$C$25))/SUM(IF(ISNUMBER(MATCH($A$3:$A$25,$E$3:$E$12,0)),B3:B25))

Thanks for all your help

[NBVC]

No Problem

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save