| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help finding string them averaging the total
I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product.. Now, there is always more than 1 instance of the product # because this list is referencing what each product sold to DIFFERENT customers for.. So say the list looks like this ___COL A_____ _____COL B_____ ASF1234GHLZ 19.00 ASF1234GHLZ 20.00 ASF1234GHLZ 35.00 ASF1234GHLZ 21.00 B3223424LHZ 5.0 (expect my list is now about 2,000 entries) I need to be able to type €śASF1234GHLZ€ť into a separate cell and I want excel to find all instances on €śASF1234GHLZ€ť & get there prices (19.00,20.00,35.00,21.00) , then add the prices together and give me the average of the prices. There may be 1 instance of a product, or there may be 20 instances of the product. I obviously know how-to do the math part in excel, what I dont know is how to make it search for that criteria and gather all of the prices from the adjacent cells then add & divide them. Any and all help you can give me is greatly appreciated! Thanks, Brandon Roland |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help finding string them averaging the total
=AVERAGE(IF(A1:A5=D1,B1:B5))
D1=€śASF1234GHLZ€ť Enter with Ctrl+Shift+Enter (array formula) "brandon roland" wrote: I have two lists , column A & B , in column A is the # for the product€¦ i.e.: A334GHLZ , in column C I have the price for that product.. Now, there is always more than 1 instance of the product # because this list is referencing what each product sold to DIFFERENT customers for.. So say the list looks like this ___COL A_____ _____COL B_____ ASF1234GHLZ 19.00 ASF1234GHLZ 20.00 ASF1234GHLZ 35.00 ASF1234GHLZ 21.00 B3223424LHZ 5.0 (expect my list is now about 2,000 entries) I need to be able to type €śASF1234GHLZ€ť into a separate cell and I want excel to find all instances on €śASF1234GHLZ€ť & get there prices (19.00,20.00,35.00,21.00) , then add the prices together and give me the average of the prices. There may be 1 instance of a product, or there may be 20 instances of the product. I obviously know how-to do the math part in excel, what I dont know is how to make it search for that criteria and gather all of the prices from the adjacent cells then add & divide them. Any and all help you can give me is greatly appreciated! Thanks, Brandon Roland |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help finding string them averaging the total
Hi,
Try, =(SUMPRODUCT((A1:A500=E1)*(B1:B500))/COUNTIF(A1:A500,E1)) Where E1 is you search string Mike "brandon roland" wrote: I have two lists , column A & B , in column A is the # for the product€¦ i.e.: A334GHLZ , in column C I have the price for that product.. Now, there is always more than 1 instance of the product # because this list is referencing what each product sold to DIFFERENT customers for.. So say the list looks like this ___COL A_____ _____COL B_____ ASF1234GHLZ 19.00 ASF1234GHLZ 20.00 ASF1234GHLZ 35.00 ASF1234GHLZ 21.00 B3223424LHZ 5.0 (expect my list is now about 2,000 entries) I need to be able to type €śASF1234GHLZ€ť into a separate cell and I want excel to find all instances on €śASF1234GHLZ€ť & get there prices (19.00,20.00,35.00,21.00) , then add the prices together and give me the average of the prices. There may be 1 instance of a product, or there may be 20 instances of the product. I obviously know how-to do the math part in excel, what I dont know is how to make it search for that criteria and gather all of the prices from the adjacent cells then add & divide them. Any and all help you can give me is greatly appreciated! Thanks, Brandon Roland |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help finding string them averaging the total
Toppers,
thanks for the quick reply.. it works except it is giving me the average for all my cell entries in B1 - B5 , not only the average of cells that have ASF1234GHLZ in them say COL A COL B ASF1234GHLZ 1.00 ASF1234GHLZ 2.00 B12345DSFSF 3.00 DFS3453SDFD 2.00 ASF1234GHLZ 2.00 your code gives me the average of everything in column B , which in this instance would be 1+2+3+2+2 = 10/5 = 2 I need it to only add the amount in B# if the string in A# is the same as the query.. which would be 1+2+2 = 5/3 = 1.66667 Thanks though maybe you can still help me figure it out! "Toppers" wrote: =AVERAGE(IF(A1:A5=D1,B1:B5)) D1=€śASF1234GHLZ€ť Enter with Ctrl+Shift+Enter (array formula) "brandon roland" wrote: I have two lists , column A & B , in column A is the # for the product€¦ i.e.: A334GHLZ , in column C I have the price for that product.. Now, there is always more than 1 instance of the product # because this list is referencing what each product sold to DIFFERENT customers for.. So say the list looks like this ___COL A_____ _____COL B_____ ASF1234GHLZ 19.00 ASF1234GHLZ 20.00 ASF1234GHLZ 35.00 ASF1234GHLZ 21.00 B3223424LHZ 5.0 (expect my list is now about 2,000 entries) I need to be able to type €śASF1234GHLZ€ť into a separate cell and I want excel to find all instances on €śASF1234GHLZ€ť & get there prices (19.00,20.00,35.00,21.00) , then add the prices together and give me the average of the prices. There may be 1 instance of a product, or there may be 20 instances of the product. I obviously know how-to do the math part in excel, what I dont know is how to make it search for that criteria and gather all of the prices from the adjacent cells then add & divide them. Any and all help you can give me is greatly appreciated! Thanks, Brandon Roland |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help finding string them averaging the total
Mike that works!
Thanks bro! - BROLL "Mike H" wrote: Hi, Try, =(SUMPRODUCT((A1:A500=E1)*(B1:B500))/COUNTIF(A1:A500,E1)) Where E1 is you search string Mike "brandon roland" wrote: I have two lists , column A & B , in column A is the # for the product€¦ i.e.: A334GHLZ , in column C I have the price for that product.. Now, there is always more than 1 instance of the product # because this list is referencing what each product sold to DIFFERENT customers for.. So say the list looks like this ___COL A_____ _____COL B_____ ASF1234GHLZ 19.00 ASF1234GHLZ 20.00 ASF1234GHLZ 35.00 ASF1234GHLZ 21.00 B3223424LHZ 5.0 (expect my list is now about 2,000 entries) I need to be able to type €śASF1234GHLZ€ť into a separate cell and I want excel to find all instances on €śASF1234GHLZ€ť & get there prices (19.00,20.00,35.00,21.00) , then add the prices together and give me the average of the prices. There may be 1 instance of a product, or there may be 20 instances of the product. I obviously know how-to do the math part in excel, what I dont know is how to make it search for that criteria and gather all of the prices from the adjacent cells then add & divide them. Any and all help you can give me is greatly appreciated! Thanks, Brandon Roland |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help finding string them averaging the total
Did you enter with Ctrl+Shift+Enter ... NO! so you get an answer of 2.
Do it correctly and you get the correct answer. "brandon roland" wrote: Toppers, thanks for the quick reply.. it works except it is giving me the average for all my cell entries in B1 - B5 , not only the average of cells that have ASF1234GHLZ in them say COL A COL B ASF1234GHLZ 1.00 ASF1234GHLZ 2.00 B12345DSFSF 3.00 DFS3453SDFD 2.00 ASF1234GHLZ 2.00 your code gives me the average of everything in column B , which in this instance would be 1+2+3+2+2 = 10/5 = 2 I need it to only add the amount in B# if the string in A# is the same as the query.. which would be 1+2+2 = 5/3 = 1.66667 Thanks though maybe you can still help me figure it out! "Toppers" wrote: =AVERAGE(IF(A1:A5=D1,B1:B5)) D1=€śASF1234GHLZ€ť Enter with Ctrl+Shift+Enter (array formula) "brandon roland" wrote: I have two lists , column A & B , in column A is the # for the product€¦ i.e.: A334GHLZ , in column C I have the price for that product.. Now, there is always more than 1 instance of the product # because this list is referencing what each product sold to DIFFERENT customers for.. So say the list looks like this ___COL A_____ _____COL B_____ ASF1234GHLZ 19.00 ASF1234GHLZ 20.00 ASF1234GHLZ 35.00 ASF1234GHLZ 21.00 B3223424LHZ 5.0 (expect my list is now about 2,000 entries) I need to be able to type €śASF1234GHLZ€ť into a separate cell and I want excel to find all instances on €śASF1234GHLZ€ť & get there prices (19.00,20.00,35.00,21.00) , then add the prices together and give me the average of the prices. There may be 1 instance of a product, or there may be 20 instances of the product. I obviously know how-to do the math part in excel, what I dont know is how to make it search for that criteria and gather all of the prices from the adjacent cells then add & divide them. Any and all help you can give me is greatly appreciated! Thanks, Brandon Roland |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| averaging a total out over a month | Excel Discussion (Misc queries) | |||
| averaging a running total | Excel Discussion (Misc queries) | |||
| Averaging a running total | Excel Discussion (Misc queries) | |||
| Need to write 3D formula averaging total sales between 3 workshee | Excel Worksheet Functions | |||
| Averaging Columns based on a Text String | New Users to Excel | |||
Linear Mode
