• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weighted average in flat file

juanito

juanito

Member
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!

72345
 

Attachments

  • WeightedAverage.xlsx
    11 KB · Views: 13
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!
 
juanito
If for some reason You would like to solve more lines than those six, then You could test this sample.
Press [ Solve ]-button.
 

Attachments

  • WeightedAverage.xlsb
    19.3 KB · Views: 8
John Jairo V said:
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!
Click to expand...
Thank you John for your reply!
I’ll respond tomorrow in euro time.
 
Another formula option,

In G3, formula :

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

72355
 
John Jairo V said:
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!
Click to expand...

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...
 
bosco_yip said:
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
Click to expand...

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...
 
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.
 
vletm said:
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.
Click to expand...

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?
 
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!
 

Attachments

  • WeightedAverage.xlsb
    19.5 KB · Views: 1
vletm said:
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!
Click to expand...
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).
 
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?
 
vletm said:
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?
Click to expand...
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.
 
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!
 

Attachments

  • WeightedAverage.xlsb
    19.9 KB · Views: 5
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
 
Hi again @juanito!

juanito said:
... need to build a 2D table within itself (just like my manual solution shown), before evaluating to a single % output value ...
Click to expand...
Check this formula:
PHP: 
=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!
 

Attachments

  • WeightedAverage.xlsx
    11.2 KB · Views: 7
juanito said:
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).
Click to expand...
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.
 

Attachments

  • Chandoo45408WeightedAverage.xlsx
    22.3 KB · Views: 6
p45cal said:
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.
Click to expand...
Impressive stuff p45cal - some non-interface M coding in there, it would appear? Many thanks...
 
John Jairo V said:
Hi again @juanito!


Check this formula:
PHP: 
=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!
Click to expand...
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.
 
Again using LET
Code: 
= 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.
 

Attachments

  • Chandoo45408WeightedAverage.xlsx
    17.1 KB · Views: 3
Peter Bartholomew said:
Again using LET
Code: 
= 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.
Click to expand...
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
 
You must log in or register to reply here.
Back
Top