Calculate weighted averages from a subset of data.

[dunlop407703]

Hi all,

I have a subset example of my data below to help with this question.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Seam[/TD]
[TD]TK*RD[/TD]
[TD]ASH[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]1.8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

What I need to be able to do is calculate the weighted average 'ASH' value when the 'SEAM' column equals "A".

To do this for the data as it sits I can handle, SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5). The issue I have is that I have a very large data set which is not sorted that I need to search for all the "A" seam data then use that to generate the weighted average for the "A" seam.

I have considered pulling all the data for each seam into separate work sheets but there could end up being up to 50 seams and I don't particularly want 50 worksheets. My aim is to have one worksheet where I have a row for each seam and a column for ash (and the other variables) which calculates the weighted averages.

Any help on this issue would be very appreciated.

Thanks in advance.

 

[MarcelBeug]

Various possibilities, depending on whether you can add a helper column or sort data on Seam, in order of (my) prefernece:

1. Helper column and SUMIFS.
Add a helper column, e.g. D2 = B2*C2 and copy down.
In the other sheet with seam in A2 and down, enter in B2 and copy down:

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2)/SUMIFS(Sheet1!B:B,Sheet1!A:A,A2)

2. Sort data on Seam.
In the other sheet with seam in A2 and down, add helper columns B and C with start and end row; and average in D. In row 2 and copy down:

B2: =MATCH($A2,Sheet1!$A:$A,0)
C2: =MATCH($A2,Sheet1!$A:$A)
D2: =SUMPRODUCT(INDEX(Sheet1!$B:$B,$B2):INDEX(Sheet1!$B:$B,$C2),INDEX(Sheet1!$C:$C,$B2):INDEX(Sheet1!$C:$C,$C2)/SUM(INDEX(Sheet1!$B:$B,$B2):INDEX(Sheet1!$B:$B,$C2)))

3. No sort, no helper: SUMPRODUCT.
Same layout, average formula in B2 and down for data e.g. from row 2:400000:

=SUMPRODUCT(Sheet1!$B2:$B400000*Sheet1!$C2:$C400000*(Sheet1!$A2:$A400000=$A2))/SUMIFS(Sheet1!$B:B,Sheet1!$A:$A,$A2)

 

[dunlop407703]

Various possibilities, depending on whether you can add a helper column or sort data on Seam, in order of (my) prefernece:

1. Helper column and SUMIFS.
Add a helper column, e.g. D2 = B2*C2 and copy down.
In the other sheet with seam in A2 and down, enter in B2 and copy down:

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2)/SUMIFS(Sheet1!B:B,Sheet1!A:A,A2)

Thank you Marcel, I am able to add a helper column so your 1st solution works wonderfully, I am very appreciative.

Can I ask if having the formula reference the whole columns slows it down as opposed to giving it a range to stop at, i.e. D1:D10000 rather than D:D?

Thanks again.

 

[Aladin Akyurek]

In A1 enter:

=MATCH(9.99999999999999E+307,B:B)

Select A1 and name the selection Lrow via the Name Box.

Now you can also invoke:

=SUMPRODUCT(--(A2:INDEX(A:A,Lrow)="SEAM"),B2:INDEX(B:B,Lrow),
    C2:INDEX(C:C,Lrow))/SUMIFS(B2:INDEX(B:B,Lrow),A2:INDEX(A:A,Lrow),"SEAM")

This will include new records automatically.

 

[dunlop407703]

In A1 enter:

=MATCH(9.99999999999999E+307,B:B)

Select A1 and name the selection Lrow via the Name Box.

Now you can also invoke:

=SUMPRODUCT(--(A2:INDEX(A:A,Lrow)="SEAM"),B2:INDEX(B:B,Lrow),
    C2:INDEX(C:C,Lrow))/SUMIFS(B2:INDEX(B:B,Lrow),A2:INDEX(A:A,Lrow),"SEAM")

This will include new records automatically.

Hi Aladin,

Thank you for your reply. After trying Marcel's solutions further I have found that I over simplified what I am trying to achieve and Marcels solutions do not work how I need them to.

I have tried to enter your formula but I get a #DIV/0 error. I copy and pasted your formula exactly.

The issue I have is that it isn't just an 'Ash' column I need the weighted average for but there are over 50 columns which I need to get the weighted average for by seam.

Any further assistance would be greatly appreciated.

Cheers

 

[MarcelBeug]

Can I ask if having the formula reference the whole columns slows it down as opposed to giving it a range to stop at, i.e. D1:D10000 rather than D:D?

If I'm correct, that shouldn't make any difference as SUMIFS only uses the used range. Unless you have other data below the list.

With regard to the solution provided by Aladin you need to replace "SEAM" with the actual seam value (2x) and the name LRow should be defined as
=MATCH(9.99999999999999E+307,$B:$B)
with column B fixed.

 

[Aladin Akyurek]

Hi Aladin,

Thank you for your reply. After trying Marcel's solutions further I have found that I over simplified what I am trying to achieve and Marcels solutions do not work how I need them to.

I have tried to enter your formula but I get a #DIV/0 error. I copy and pasted your formula exactly.

The issue I have is that it isn't just an 'Ash' column I need the weighted average for but there are over 50 columns which I need to get the weighted average for by seam.

Any further assistance would be greatly appreciated.

Cheers

To where did you copy the formula?

 

[Aladin Akyurek]

If I'm correct, that shouldn't make any difference as SUMIFS only uses the used range. Unless you have other data below the list.

With regard to the solution provided by Aladin you need to replace "SEAM" with the actual seam value (2x) and the name LRow should be defined as
=MATCH(9.99999999999999E+307,$B:$B)
with column B fixed.

Marcel,

Since the definition is local to the sheet (done in A1), no locking (dollarizing) is needed.

 

[MarcelBeug]

Ah, my mistake: A1 is a helper field.
My suggestion would be OK if LRow would be defined with the formula directly, without helper field.

 

[Aladin Akyurek]

Ah, my mistake: A1 is a helper field.
My suggestion would be OK if LRow would be defined with the formula directly, without helper field.

Replacing Lrow with its definition in many formulas will cost some processing cycles though...