seperating a group of common cells

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!

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

"ShineboxNJ" wrote:

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

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

"ShineboxNJ" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "ShineboxNJ" wrote:
>
>> 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!

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

ShineboxNJ wrote:
> 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!

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


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Building a pivoat table from your data is also an option to summarize it.
>
> ShineboxNJ wrote:
>> 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!