Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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" wrote in message ... 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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
check out
http://www.contextures.com/tiptech.html for lots of ideas on the use of pivot tables. "Aladin Akyurek" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
define group of cells | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
copy group of cells to another group of cells using "IF" in third | Excel Worksheet Functions | |||
How can I group a bunch of cells together, so I can sort by colum. | Excel Worksheet Functions |