seperating a group of common cells

[ShineboxNJ]

On my worksheet I have a list of products in column O, and I have the
quantity of received products in column Q. Column O has a lot of repeat
products with different quantities. I need to take an average of those
quantities matched to their respective skus and put it on a new worksheet.

AAC LOUNGEEP2 5
AAC LOUNGEEP2 10
AAC LOUNGEEP2 6

I want to be able to have AAC LOUNGEEP2 on a seperate worksheet with the
average quantity.

AAC LOUNGEEP2 7

I can do the average with no problem, but i want to write a formula that
knows to match the skus and than take the average.
Any ideas?

Thanks!

 

[ShineboxNJ]

actually... the best scenario would be this:
A 5
A 10
A 25
A 30
A Average 17.5
B 22
B 15
B 10
B Average 15.66666667
C 3
C 2
C 8
C 1
C Average 3.5

so then i could look up the average total in my seperate tabbed worksheet.
i feel like a match formula would work somehow, but I am not sure.

thanks again

 

[JulieD]

Hi

if you can sort your data (use data / sort) to sort it, then you might like
to have a look at data / subtotals - using the average function

or

you can use sumif & countif to generate an average

=SUMIF(A1:A100,"A",B1:B100)/COUNTIF(A1:A100,"A")
where column A has the product name, "A" is the product that you're
interested in and column B has the value

if you put a list of your products on another sheet you can edit the formula
as follows
=SUMIF(Sheet1!A1:A100,A1,Sheet1!B1:B100)/COUNTIF(Sheet1!A1:A100,A1)
where A1 holds the product name you're interested in

Hope this helps

Cheers
JulieD

 

[Aladin Akyurek]

Building a pivoat table from your data is also an option to summarize it.

 

[JulieD]

check out
http://www.contextures.com/tiptech.html
for lots of ideas on the use of pivot tables.