I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset. Let me know if this doesn't make sense but hopefully the attached example helps. Thanks!
Formula to calculate avg/wtd avg on a subset of a list
-
-
Re: Formula to calculate avg/wtd avg on a subset of a list
Hi,
I'm not sure HOW to want to use the "Weight" Column... and I presume the "Weight" means an importance, as opposed to a physical weight such as pounds/kilos
To get a standard average of the products listed, enter this in B2 and copy down as required.
=SUMIF('Whole List'!$A$2:$A$9,$A2,'Whole List'!$B$2:$B$9) / COUNTIF('Whole List'!$A$2:$A$9,Subset!$A2)
If you can give an example of how "Weight" should be used and the expected outcome, maybe I can help some more.
HTH
Ger -
Re: Formula to calculate avg/wtd avg on a subset of a list
Maybe...
For average:
B2, confirmed with CONTROL+SHIFT+ENTER, and copied down:
=AVERAGE(IF('Whole List'!$A$2:$A$9=Subset!A2,'Whole List'!$B$2:$B$9))
For weighted average:
C2, confirmed with just ENTER, and copied down:
=SUMPRODUCT(--('Whole List'!$A$2:$A$9=Subset!A2),'Whole List'!$B$2:$B$9,'Whole List'!$C$2:$C$9)/SUMIF('Whole List'!$A$2:$A$9,Subset!A2,'Whole List'!$C$2:$C$9)
See attached file...
Hope this helps!
-
Re: Formula to calculate avg/wtd avg on a subset of a list
Here is one simple way that you can do it.
Note that I have removed the prefix of # to make data entry easier.
I have used a dynamic named range to allow the sub set list to be entered and grow as required.
There are other possible solutions that will depend on how you wish to select your sub set. I have assumed that you will simply type (or copy and paste) the numbers in.
An alternative method would be to use VLookup to return teh information from the main data set for the sub set and then work on that.
HTH,
Alan.
-
-
Re: Formula to calculate avg/wtd avg on a subset of a list
ok - one last try. i want this to all happen in one formula it would sort of like saying something like {=SUM(('Whole List'!A2:A9=Subset!B2)*('Whole List'!C2:C9))} except for just one condition, I want to say if the product on the whole list worksheet is equal to subset 1 OR subset 2 OR subset 3, etc. Keep in mind this is for a huge data set so an array formula is what I need rather than just using vlookups and then averaging the data. Thanks for the help so far, I think we might be getting close
-
Re: Formula to calculate avg/wtd avg on a subset of a list
I see it like this:
1. Is it possible to create a single cell formula to retrieve the wtd average based on a single product number? Yes.
2. Is it possible to create a single cell formula to retrieve the wtd average of a fixed number of product numbers? Yes.
3. Is it possible to create a single cell formula to retrieve the wtd average of a variable number of product numbers? No.
You've discovered it's possible to do 1 & 2, now you're asking for 3. I'll go out on a limb here and suggest it can't be done without creating a custom function in VBA. (or at least can't be done elegantly within the realm of my own patience vs opt for UDF threshold)
Is that the direction you'd like to go now?
-
Re: Formula to calculate avg/wtd avg on a subset of a list
Quote from st8champ3xok - one last try. i want this to all happen in one formula it would sort of like saying something like {=SUM(('Whole List'!A2:A9=Subset!B2)*('Whole List'!C2:C9))} except for just one condition, I want to say if the product on the whole list worksheet is equal to subset 1 OR subset 2 OR subset 3, etc. Keep in mind this is for a huge data set so an array formula is what I need rather than just using vlookups and then averaging the data. Thanks for the help so far, I think we might be getting close
Ger's solution and mine should give you an average for a specified subset. Also, I've included a formula for the weighted average. Have you tried them?
-
Re: Formula to calculate avg/wtd avg on a subset of a list
Quote from st8champ3xYep - but they are basically doing what I have already written a macro to do. I was trying to do everything in one formula but it doesn't sound like it is easily done. Thanks for everyone's help and patience!!
Oh I see. Try the following then...
For average:
=AVERAGE(IF(ISNUMBER(MATCH('Whole List'!A2:A9,Subset!A2:A4,0)),'Whole List'!B2:B9))
...confirmed with CONTROL+SHIFT+ENTER
For weighted average:
=SUMPRODUCT(--ISNUMBER(MATCH('Whole List'!A2:A9,Subset!A2:A4,0)),'Whole List'!B2:B9,'Whole List'!C2:C9)/SUMPRODUCT(--ISNUMBER(MATCH('Whole List'!A2:A9,Subset!A2:A4,0)),'Whole List'!C2:C9)
...confirmed with just ENTER
See attached file...
Hope this helps!
-
-
Re: Formula to calculate avg/wtd avg on a subset of a list
Quote from Domenic=SUMPRODUCT(--ISNUMBER(MATCH('Whole List'!A2:A9,Subset!A2:A4,0)),'Whole List'!B2:B9,'Whole List'!C2:C9)/SUMPRODUCT(--ISNUMBER(MATCH('Whole List'!A2:A9,Subset!A2:A4,0)),'Whole List'!C2:C9)
Well I guess that's not 'too' bad is it?
Unfortunately, I don't 'think' you'll find anything more elegant than this, aside from the UDF alternative.
Domenic has considerably more patience than I.
-
Re: Formula to calculate avg/wtd avg on a subset of a list
Quote from Aaron BloodDomenic has considerably more patience than I.
'Patience' is my middle name... <VBG> But I hope I understood correctly...
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!