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.

 

[dunlop407703]

To where did you copy the formula?

Hey Aladin and Marcel,

Thank you again.

Aladin I got your formula to work by replacing "Seam" with a cell reference to the seam I want to work with i.e ref E2 which contains A.

You both lost me a little talking about the definitions and locking the B:B reference. At the moment I have entered Aladins formula as provided, changed the seam reference, and then used across many more columns and everything seams to be working fine, no noticeable lag in processing time when using the workbook.

Can I ask what the formula in A1 actually does in your solution Aladin?

Cheers

 

[Aladin Akyurek]

Hey Aladin and Marcel,

Thank you again.

Aladin I got your formula to work by replacing "Seam" with a cell reference to the seam I want to work with i.e ref E2 which contains A.

You both lost me a little talking about the definitions and locking the B:B reference. At the moment I have entered Aladins formula as provided, changed the seam reference, and then used across many more columns and everything seams to be working fine, no noticeable lag in processing time when using the workbook.

Can I ask what the formula in A1 actually does in your solution Aladin?

Cheers

The formula of A1 computes the row of the last data record at any time.

A2:INDEX(A:A,$A$1) means the range from A2 to the last record in column A. Put otherwise, it's a dynamic range.

 

[dunlop407703]

The formula of A1 computes the row of the last data record at any time.

A2:INDEX(A:A,$A$1) means the range from A2 to the last record in column A. Put otherwise, it's a dynamic range.

Awesome, thank you so much, I really appreciate your help.

One last question while I have you. In my actual workbook this formula will go in a different worksheet and therefore all the references will be "worksheet1!A:A" etc. Is there a way to cause all the references in the formula to go to the other worksheet by putting that worksheets reference out the front of the whole formula, rather than putting it before every individual cell reference?

 

[Aladin Akyurek]

Awesome, thank you so much, I really appreciate your help.

One last question while I have you. In my actual workbook this formula will go in a different worksheet and therefore all the references will be "worksheet1!A:A" etc. Is there a way to cause all the references in the formula to go to the other worksheet by putting that worksheets reference out the front of the whole formula, rather than putting it before every individual cell reference?

Are you using A1 or not?

 

[dunlop407703]

Are you using A1 or not?

Yes I have "Lrow" in the A1 cell on the DATABASE worksheet where the data is stored, and referenced as DATABASE!Lrow from the Seam Summaries worksheet where I am trying to compute the weighted averages.

The formula as I am trying to use it is below. "A7" is the cell where the seam I am trying to find is stored. Also in my DATABASE sheet the data doesn't start until row 6 hence that reference in the dynamic indexes.

=SUMPRODUCT(--(DATABASE!D6:INDEX(DATABASE!D:D,DATABASE!Lrow)=A7),DATABASE!K6:INDEX(DATABASE!K:K,DATABASE!Lrow),A7:INDEX(DATABASE!J:J,DATABASE!Lrow))/SUMIFS(DATABASE!K6:INDEX(DATABASE!K:K,DATABASE!Lrow),DATABASE!D6:INDEX(DATABASE!D:D,DATABASE!Lrow),A7)

 

[dunlop407703]

Yes I have "Lrow" in the A1 cell on the DATABASE worksheet where the data is stored, and referenced as DATABASE!Lrow from the Seam Summaries worksheet where I am trying to compute the weighted averages.

The formula as I am trying to use it is below. "A7" is the cell where the seam I am trying to find is stored. Also in my DATABASE sheet the data doesn't start until row 6 hence that reference in the dynamic indexes.

Apologies Aladin,

I have found my error in the formula I was using and it is working wonderfully. I am now trying to add a second criteria as I have just found in my data that not all values that meet the "Seam" name requirement are actually included in the wt avg's. Frustrating data set to say the least.

 

[dunlop407703]

Are you using A1 or not?

Aladin I'm hoping you can help once more.

My wt avg's are functioning perfectly. I am stumped on what I thought would be a simpler task though. For each set of "Seam" data in addition to the wt avg I also need a formula to calculate the min value and also the max value.

I have tried everything I can think of. I have utilised your dynamic range function but can't get it to bring up the right data to apply the min or max to, I have also tried Index Matching but also to no avail.

If you have a solution would be amazing.

Cheers

 

[Aladin Akyurek]

Apologies Aladin,

I have found my error in the formula I was using and it is working wonderfully. I am now trying to add a second criteria as I have just found in my data that not all values that meet the "Seam" name requirement are actually included in the wt avg's. Frustrating data set to say the least.

You have probably errand spaces around the entries which makes the formula to skip them.

 

[Aladin Akyurek]

Aladin I'm hoping you can help once more.

My wt avg's are functioning perfectly. I am stumped on what I thought would be a simpler task though. For each set of "Seam" data in addition to the wt avg I also need a formula to calculate the min value and also the max value.

I have tried everything I can think of. I have utilised your dynamic range function but can't get it to bring up the right data to apply the min or max to, I have also tried Index Matching but also to no avail.

If you have a solution would be amazing.

Cheers

Care to elaborate? Min/max of what and for which conditions?

 

[dunlop407703]

Care to elaborate? Min/max of what and for which conditions?

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.