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.

 

[Aladin Akyurek]

For the same data I have conducted wt avg's on, I also need to find the max and min values.

For example the formula you provided for the wt avg's found all the data which was for seam "S" and wt avg'd the ash. For that same selection out of "A" seam data I also need to get the min value, and the max value.

For whatever reason when I pull your variable index array from out of the formula you provided I can't see to find a way to use it to get the max and min.

You still did not say of which range the MIN/MAX must be computed. Weighted average multiplies multiple ranges before averaging...

 

[dunlop407703]

You still did not say of which range the MIN/MAX must be computed. Weighted average multiplies multiple ranges before averaging...

Sorry Aladin, I don't think I understand your question.

The min and max need to be found for the same range that the weighted average is found on. For example if I have the same data set as my original post, we calculated the wt avg on the range where Seam=A, I need the min and max values for that same range when Seam=A.

 

[Aladin Akyurek]

For the same data I have conducted wt avg's on, I also need to find the max and min values.

For example the formula you provided for the wt avg's found all the data which was for seam "S" and wt avg'd the ash. For that same selection out of "A" seam data I also need to get the min value, and the max value.

For whatever reason when I pull your variable index array from out of the formula you provided I can't see to find a way to use it to get the max and min.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td] Seam[/td][td] TK*RD[/td][td] ASH[/td][td][/td][td][/td][td] TK*RD[/td][td] ASH[/td][/tr]


[tr][td]

2​

[/td][td] A[/td][td] 2[/td][td] 5[/td][td][/td][td] A[/td][td] 1.8[/td][td] 5[/td][/tr]


[tr][td]

3​

[/td][td] A[/td][td] 1.8[/td][td] 6[/td][td][/td][td] B[/td][td] 3[/td][td] 10[/td][/tr]


[tr][td]

4​

[/td][td] A[/td][td] 2[/td][td] 5[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td] B[/td][td] 3[/td][td] 10[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 control+shift+enter, not just enter, copy across, and down:

=MIN(IF($A$2:$A$5=$E2,B$2:B$5))

Replace MIN with MAX for max values.

If you are on 2016, you can invoke MINIFS and MAXIFS instead (which do not require array-processing).

 

[dunlop407703]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Seam[/TD]
[TD] TK*RD[/TD]
[TD] ASH[/TD]
[TD][/TD]
[TD][/TD]
[TD] TK*RD[/TD]
[TD] ASH[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD] A[/TD]
[TD] 2[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD] A[/TD]
[TD] 1.8[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD] A[/TD]
[TD] 1.8[/TD]
[TD] 6[/TD]
[TD][/TD]
[TD] B[/TD]
[TD] 3[/TD]
[TD] 10[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] A[/TD]
[TD] 2[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD] B[/TD]
[TD] 3[/TD]
[TD] 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In F2 control+shift+enter, not just enter, copy across, and down:

=MIN(IF($A$2:$A$5=$E2,B$2:B$5))

Replace MIN with MAX for max values.

If you are on 2016, you can invoke MINIFS and MAXIFS instead (which do not require array-processing).

Thank you Aladin,

I couldn't get your first solution to work but as I have 2016 I tried your second solution. By setting the lookup array as dynamic ranges from your previous formula I was able to get the outcome I was after.

=MINIFS(DATABASE!$J$6:INDEX(DATABASE!$J:$J,DATABASE!Lrow),DATABASE!$M$6:INDEX(DATABASE!$M:$M,DATABASE!Lrow),B16)

Thanks you very much for you help.

Before I close this off using the dynamic ranges has been an awesome learning curve. But I came across a part I don't understand. In your original formula as below, when I evaluate each step, once $J$6:INDEX($J$6:Lrow) rationalizes to say $J$6:$J$100 it then gives all that data {(0,0,0.1,0.2,0.3)} etc. However when I pulled that dynamic range out ad put it behind a "SUM" or "MIN" statement such as SUM($J$6:INDEX($J$6:Lrow)) it kept retrieving no data and returning "" once the range was queried. I may not have explained that well but do you know what was happening?

=SUMPRODUCT(--(DATABASE!$M$6:INDEX(DATABASE!$M:$M,DATABASE!Lrow)=$B18),DATABASE!$K$6:INDEX(DATABASE!$K:$K,DATABASE!Lrow),DATABASE!P$6:INDEX(DATABASE!P:P,DATABASE!Lrow))/SUMIFS(DATABASE!$K$6:INDEX(DATABASE!$K:$K,DATABASE!Lrow),DATABASE!$M$6:INDEX(DATABASE!$M:$M,DATABASE!Lrow),$B18)

Cheers

Ben

 

[Aladin Akyurek]

Thank you Aladin,

I couldn't get your first solution to work but as I have 2016 I tried your second solution. By setting the lookup array as dynamic ranges from your previous formula I was able to get the outcome I was after.

MINIFS/MAXIFS are faster than the array-processing counterparts, which by the way also work, if done properly. Control+shift+enter >> Press down the control and the shift keys at the same time while you hit the enter key. If this succeeds, Excel itself puts a pair of { and } around the formula in recognition.

Thanks you very much for you help.

You are welcome.

Before I close this off using the dynamic ranges has been an awesome learning curve. But I came across a part I don't understand. In your original formula as below, when I evaluate each step, once $J$6:INDEX($J$6:Lrow) rationalizes to say $J$6:$J$100 it then gives all that data {(0,0,0.1,0.2,0.3)} etc. However when I pulled that dynamic range out ad put it behind a "SUM" or "MIN" statement such as SUM($J$6:INDEX($J$6:Lrow)) it kept retrieving no data and returning "" once the range was queried. I may not have explained that well but do you know what was happening?
...

SUM($J$6:INDEX($J$6:Lrow)

should be:

SUM($J$6:INDEX($J:$J,Lrow))