# Formula to calculate avg/wtd avg on a subset of a list

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

## Files

• 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

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

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

## Files

• Re: Formula to calculate avg/wtd avg on a subset of a list

What I am thinking is of someway to use an array to that would do basically what an array sumif, averageif, countif, etc. would do by saying only average the prices of the subset products. This would use just 1 formula and would save a ton of space. I wish I could use the real data but I will have stick to the example that I attached. Let's say out a customer ordered 3 of the "products" listed on the subset page, I want to calculate their average price and average weight. I know I could go lookup the data with a formula from the Whole list worksheet and then perform a simple average but doing this on the scale that I am doing it adds about 4MB to the file.

• 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.

## Files

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

[SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

• Re: Formula to calculate avg/wtd avg on a subset of a list

Quote from st8champ3x

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

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

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

• Re: Formula to calculate avg/wtd avg on a subset of a list

Quote from st8champ3x

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

## Files

• 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.

[SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

• Re: Formula to calculate avg/wtd avg on a subset of a list

Quote from Aaron Blood

Domenic has considerably more patience than I.

'Patience' is my middle name... <VBG> But I hope I understood correctly...

• Re: Formula to calculate avg/wtd avg on a subset of a list

Now that's what I'm talking about! Thank you!!!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!