Weighted Average of Non Numeric Data

[lil_ern63]

I believe the description is the best way to describe my question. In the attached I have two tables of data. The Conn Qty (column C) is broken down into qty by depth in the lower data set. What I would like to determine is the most common, or weighted average?, of what the chart calls "Load". Loads are are an alpha designation A-L or can be blank.

The attached example has 52 conns from 0-11.4 deep. Of those, 19 are labeled with a load "A", while the rest are of various other call outs (blank, B, F, C or H). Since "A" makes up the most common (19/52, or 36%) the yellow highlighted cell would return A.

I know I could get this figured out with helper columns, etc. but this is included in a much larger sheet and I am attempting to determine the value without much extra side data. Not sure if the "Blank" option throws this into a more complex questions also.

As always, all help is appreciated.

[GeoffW283]

I tried my best but I can't solve this without a 9 x 11 helper "table". However, if the concern with helpers is finding an appropriate place on an already busy worksheet then one idea might be to relocate the helper table to it's own worksheet if that would be acceptable. That is what I have done here and I will post it as nobody else (yet) has come up with a helperless solution.

I created a new worksheet called "helper" as follows:

weightedAvg.png

The formulas on the "helper" worksheet table are your "Qty By Depth" formula with an added condition that selects for the load in row-1, so in A2 copied down and across to I11:

Please Login or Register  to view this content.

Column-I caters for blanks in the "Load" column.

The final answer with the following formula is in main!G23 copied down:

Please Login or Register  to view this content.

Attached is your workbook with the above changes.

Hopefully this is some use to you pending someone posting a "helperless" solution.

[XOR LX]

Hi,

So a 'blank' Load is an equally permissible return?

Perhaps, in G23:

=IF(COUNTIFS(A$3:A$18,">="&B23,A$3:A$18,"<"&C23),T(LOOKUP(1,0/FREQUENCY(0,1/(1+SUMIFS(C$3:C$18,A$3:A$18,">="&B23,A$3:A$18,"<"&C23,B$3:B$18,B$3:B$18&""))),B$3:B$18)),"No Solution")

and copied down.

Regards

[lil_ern63]

XOR LX,

This works great! This is exactly what I was looking for. The only alteration I will make is changing "No Solution" to return a blank, as this is the default Load in my scenario.
Thank you for your help!

Geoff,

Your solution is also appreciated. It is along the lines of what I was thinking myself, with the use of helper columns.

Both solutions are greatly appreciated.

Thank you!

Marking Solved!

[XOR LX]

You're welcome!

Cheers

[GeoffW283]

Thanks for the feedback. Glad you got your "helperless" solution. Now I have some homework to do to understand XOR LX's solution