Weighted average in flat file

[juanito]

Hi all - I want to find a formula which can extract a weighted average where there is only one Values column. I can do this by pivoting into two columns easily enough (see solution to the right), but prefer to find a formula which can act directly on the data table as presented... and I haven't been able to do this by myself.

In the example attached, I want to evaluate the average of Quality, weighted by Hours.

Thanks in advance!

 

[John Jairo V]

Hi @juanito!

Check this formula, like an option:
=MMULT(INDEX(Table22[Value],N(IF(1,{1,3,5}))),INDEX(Table22[Value],N(IF(1,{2;4;6}))))/SUM(INDEX(Table22[Value],N(IF(1,{2;4;6}))))

Or, with Microsoft 365:
=LET(h,INDEX(Table22[Value],{2;4;6}),MMULT(INDEX(Table22[Value],{1,3,5}),h)/SUM(h))

Blessings!

 

[vletm]

juanito
If for some reason You would like to solve more lines than those six, then You could test this sample.
Press [ Solve ]-button.

 

[juanito]

Hi @juanito!

Check this formula, like an option:
=MMULT(INDEX(Table22[Value],N(IF(1,{1,3,5}))),INDEX(Table22[Value],N(IF(1,{2;4;6}))))/SUM(INDEX(Table22[Value],N(IF(1,{2;4;6}))))

Or, with Microsoft 365:
=LET(h,INDEX(Table22[Value],{2;4;6}),MMULT(INDEX(Table22[Value],{1,3,5}),h)/SUM(h))

Blessings!

Thank you John for your reply!
I’ll respond tomorrow in euro time.

 

[juanito]

juanito
If for some reason You would like to solve more lines than those six, then You could test this sample.
Press [ Solve ]-button.

Thanks vletm! Going to sleep now but more tomorrow I promise!

 

[bosco_yip]

Another formula option,

In G3, formula :

=SUMPRODUCT((D3:D99="Quality")*E3:E99,(D4:D100="Hours")*E4:E100)/SUMPRODUCT((D4:D100="Hours")*E4:E100)

 

[juanito]

Hi @juanito!

Check this formula, like an option:
=MMULT(INDEX(Table22[Value],N(IF(1,{1,3,5}))),INDEX(Table22[Value],N(IF(1,{2;4;6}))))/SUM(INDEX(Table22[Value],N(IF(1,{2;4;6}))))

Or, with Microsoft 365:
=LET(h,INDEX(Table22[Value],{2;4;6}),MMULT(INDEX(Table22[Value],{1,3,5}),h)/SUM(h))

Blessings!

Thanks John - fascinating use of MMULT, a function which I keep telling myself I need to understand better. But it doesn't give me the general solution I am seeking, which would function over a potentially larger dataset, with rows in any order etc...

 

[juanito]

Thanks vletm! Going to sleep now but more tomorrow I promise!

Thanks again vietm... only problem here is that the evaluation doesn't give the correct answer!!

 

[juanito]

Another formula option,

In G3, formula :

=SUMPRODUCT((D3:D99="Quality")*E3:E99,(D4:D100="Hours")*E4:E100)/SUMPRODUCT((D4:D100="Hours")*E4:E100)

View attachment 72355

Thanks, bosco_yip! The problem with this approach is that the rows must be ordered in pairs, correct? I am looking for a more general solution which works independently of order and references column C...

 

[vletm]

juanito
Your: only problem here is that the evaluation doesn't give the correct answer!!
If Your real data is like Your given sample data, then it will give the correct answer as other solutions.
Your sample data has six rows ...
You should give a sample data which reflects better with You real data.
What is the correct answer, which You're looking for?
A challenge is that only You seems to know ... what? others could offer something based Your given information.

 

[juanito]

juanito
Your: only problem here is that the evaluation doesn't give the correct answer!!
If Your real data is like Your given sample data, then it will give the correct answer as other solutions.
Your sample data has six rows ...
You should give a sample data which reflects better with You real data.
What is the correct answer, which You're looking for?
A challenge is that only You seems to know ... what? others could offer something based Your given information.

Hi again vletm and I sincerely appreciate you staying close to this. It is true that your example was similar in structure but the data was a little different, so I was wrong to expect the same result. I took your model and pasted in the exact same data. Your Solve evaluated to 71% when the weighted average (as defined in my model) is 72%. Or am I missing something?

 

[vletm]

juanito
Yes.
My previous solution gives 70.5% and there were one my logic mistake.
... based empty rows below Your data ( those rows have not deleted from there ).
I modified this sample and now, it gives 71.5 as Your sample with one decimal.
My solution datas no need to sorted, but there should be, by user, those two rows per OU.
The sample is always sample, because too many times, there has been done a lot of 'minor changes by user' before it would work as it should work.
Ps. This should solve You data as You have done it 'manually' ... then You could compare/verify it too --- the result is ... the result!

 

[juanito]

juanito
Yes.
My previous solution gives 70.5% and there were one my logic mistake.
... based empty rows below Your data ( those rows have not deleted from there ).
I modified this sample and now, it gives 71.5 as Your sample with one decimal.
My solution datas no need to sorted, but there should be, by user, those two rows per OU.
The sample is always sample, because too many times, there has been done a lot of 'minor changes by user' before it would work as it should work.
Ps. This should solve You data as You have done it 'manually' ... then You could compare/verify it too --- the result is ... the result!

Thanks! In this case we are still without a "general" solution. It may be that there isn't a satisfactory answer and I must find an alternative method (PQ perhaps).

 

[vletm]

juanito
What would need for Your "general" solution?
a more general solution which works independently of order and references column C...
If You cannot even give more hints, it would be challenge to try to do it?
Mine ... no matter of users give order ...
... column C ... somewhere should know - where is Your data?

 

[juanito]

juanito
What would need for Your "general" solution?
a more general solution which works independently of order and references column C...
If You cannot even give more hints, it would be challenge to try to do it?
Mine ... no matter of users give order ...
... column C ... somewhere should know - where is Your data?

Hi vletm! By a general solution I mean for a data table with any number of entries and sorted in any order. The Values should be weighted by each "OU", depending on total Hours. I believe that the initial attachment that I included should be sufficiently indicative, but I am very happy to expand as required. A formula to do this would effectively need to build a 2D table within itself (just like my manual solution shown), before evaluating to a single % output value. Thanks as always and I understand if this is too hard a challenge, or even impossible within one Excel formula. I was thinking that perhaps the dynamic range functions, or matrix multiplication even, could perform this: but this takes me too far outside my skillset and hence I posted the Question.

 

[vletm]

juanito
You mean those two options ... as I've written - solved!
to build a 2D table -- as I wrote - it's possible ... but it You ask to get the result, then You should mention it too!
Did You skipped below line?
Ps. This should solve You data as You have done it 'manually' ... then You could compare/verify it too --- the result is ... the result!
... and with this version, it would do one version of those Your named values.
... and after, You do not need those, You could take those away!

 

[juanito]

Thanks vletm! And your solution does indeed evaluate correctly, independent of row order. Well done!
Ideally I would have found a formula solution but your code does the job.
Best, juanito

 

[John Jairo V]

Hi again @juanito!

... need to build a 2D table within itself (just like my manual solution shown), before evaluating to a single % output value ...

Check this formula:

=LET(
ou,Table22[OU],var,Table22[Variable],val,Table22[Value],
q,UNIQUE(ou),f,2+ROWS(q),qah,SUMIFS(val,ou,q,var,{"Quality","Hours"}),
qu,INDEX(qah,,1),ho,INDEX(qah,,2),qh,qu*ho,
SWITCH(SEQUENCE(f),
f-1,CHOOSE({1,2,3,4},"Total",SUM(qu),SUM(ho),SUM(qh)),
f,CHOOSE({1,1,2,3},"","WaQ",SUM(qh)/SUM(ho)),
CHOOSE({1,2,3,4},q,qu,ho,qh)))

Check file with this solution applied. Blessings!

 

[p45cal]

Thanks! In this case we are still without a "general" solution. It may be that there isn't a satisfactory answer and I must find an alternative method (PQ perhaps).

PQ is your friend.
The green mini-table at cell L10 is the PQ.
Two other confirmatory calculations included, one with an extension of your own formulae, the other with a pivot table, results showing at J11:K11.

To test, change the extent of the Table at cell C2 by dragging the bottom right corner to include more or fewer countries and click Refresh All in the Data tab of the ribbon.

 

[juanito]

PQ is your friend.
The green mini-table at cell L10 is the PQ.
Two other confirmatory calculations included, one with an extension of your own formulae, the other with a pivot table, results showing at J11:K11.

To test, change the extent of the Table at cell C2 by dragging the bottom right corner to include more or fewer countries and click Refresh All in the Data tab of the ribbon.

Impressive stuff p45cal - some non-interface M coding in there, it would appear? Many thanks...

 

[juanito]

Hi again @juanito!


Check this formula:

=LET(
ou,Table22[OU],var,Table22[Variable],val,Table22[Value],
q,UNIQUE(ou),f,2+ROWS(q),qah,SUMIFS(val,ou,q,var,{"Quality","Hours"}),
qu,INDEX(qah,,1),ho,INDEX(qah,,2),qh,qu*ho,
SWITCH(SEQUENCE(f),
f-1,CHOOSE({1,2,3,4},"Total",SUM(qu),SUM(ho),SUM(qh)),
f,CHOOSE({1,1,2,3},"","WaQ",SUM(qh)/SUM(ho)),
CHOOSE({1,2,3,4},q,qu,ho,qh)))

Check file with this solution applied. Blessings!

That is awesome, sir! My (corporate) version of 365 doesn't support LET yet - however I believe I may be able to replicate the functionality using named formulas... it may take me a day or two to confirm.

 

[p45cal]

some non-interface M coding in there, it would appear?

Not in Advanced Editor, only in the formula bar.

 

[juanito]

Not in Advanced Editor, only in the formula bar.

Interesting, I will follow up

 

[Peter Bartholomew]

Again using LET

= LET(
     quality, FILTER(Value, Variable="Quality"),
     hours,   FILTER(Value, Variable="Hours"),
     SUM(quality*hours) / SUM(hours) )

Since LET has yet to be enabled in your version of 365, 'quality' and 'hours' would work as sheet scope or workbook names.
Or you could replace the functions by their formula and put up with a long expression.

The formula relies upon Quality and Hours each occurring once for any country and in the same order as one another.

 

[juanito]

Again using LET

= LET(
     quality, FILTER(Value, Variable="Quality"),
     hours,   FILTER(Value, Variable="Hours"),
     SUM(quality*hours) / SUM(hours) )

Since LET has yet to be enabled in your version of 365, 'quality' and 'hours' would work as sheet scope or workbook names.
Or you could replace the functions by their formula and put up with a long expression.

The formula relies upon Quality and Hours each occurring once for any country and in the same order as one another.

Thanks @Peter Bartholomew (and apologies for not responding earlier). Unfortunately, the order requirement doesn't suit my model and I was looking for a more general solution - which I have now found thanks to the support on this thread