Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
another excel question, thanks guys!
hello,
alot of you helped me w/ my first question.. now i have another.. to recapp, i've got a code that finds the search criteria (F2) from my list of A2 through A2000 , if the string is found, it takes the value in the adjacent column (C) adds it and produces a average for all the products found with the string entered in E2 , here is that code.. =(SUMPRODUCT((A2:A2000=F2)*(C2:C2000))/COUNTIF(A2:A2000,F2)) that code works, but i want to make another code for another cell, that will find two seperate strings (F2 & F5) , add them all up, and give me a average for two different products... at first i tried simply using this code twice, then adding both averages and dividing by two but i loose $ w/ that formula.. for instance.... product 1 returns 3 results ($1.00,$2.00,$3.00) for which the average is 2 (6/3 = 2) product 2 returns 4 results ($2.00,$3.00,$4.50,$1.50) for which the average is $2.75 (11/4 = 2.75) now, when you take the two averages $2.75 & $2.00 and divide that by 2 , you get $2.37 .. but if you add all of the products together 1+2+3+2+3+4.50+1.50 = 17 , then divide that by the amount of all products found 17/7 , you get $2.43 which is what i'm trying to do so i don't loose any money! so the code i have will work for the first instance when only 1 product is needed, but i need to modify that code to work on two strings (F2 & F5) for my 2nd product cell any and all help is greatly appriceated! Thanks, Brandon Roland |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
another excel question, thanks guys! Mike H Help!
mike H you gave me the code yesterday maybe you can help me alter it for my
2nd cell? "brandon roland" wrote: hello, alot of you helped me w/ my first question.. now i have another.. to recapp, i've got a code that finds the search criteria (F2) from my list of A2 through A2000 , if the string is found, it takes the value in the adjacent column (C) adds it and produces a average for all the products found with the string entered in E2 , here is that code.. =(SUMPRODUCT((A2:A2000=F2)*(C2:C2000))/COUNTIF(A2:A2000,F2)) that code works, but i want to make another code for another cell, that will find two seperate strings (F2 & F5) , add them all up, and give me a average for two different products... at first i tried simply using this code twice, then adding both averages and dividing by two but i loose $ w/ that formula.. for instance.... product 1 returns 3 results ($1.00,$2.00,$3.00) for which the average is 2 (6/3 = 2) product 2 returns 4 results ($2.00,$3.00,$4.50,$1.50) for which the average is $2.75 (11/4 = 2.75) now, when you take the two averages $2.75 & $2.00 and divide that by 2 , you get $2.37 .. but if you add all of the products together 1+2+3+2+3+4.50+1.50 = 17 , then divide that by the amount of all products found 17/7 , you get $2.43 which is what i'm trying to do so i don't loose any money! so the code i have will work for the first instance when only 1 product is needed, but i need to modify that code to work on two strings (F2 & F5) for my 2nd product cell any and all help is greatly appriceated! Thanks, Brandon Roland |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
another excel question, thanks guys!
try
=(SUMPRODUCT(or(A2:A2000=F2,A2+A2000=F%)*(C2:C2000 )/(COUNTIF(A2:A2000,F2)+countif(A2:A2000,F5)) "brandon roland" wrote: hello, alot of you helped me w/ my first question.. now i have another.. to recapp, i've got a code that finds the search criteria (F2) from my list of A2 through A2000 , if the string is found, it takes the value in the adjacent column (C) adds it and produces a average for all the products found with the string entered in E2 , here is that code.. =(SUMPRODUCT((A2:A2000=F2)*(C2:C2000))/COUNTIF(A2:A2000,F2)) that code works, but i want to make another code for another cell, that will find two seperate strings (F2 & F5) , add them all up, and give me a average for two different products... at first i tried simply using this code twice, then adding both averages and dividing by two but i loose $ w/ that formula.. for instance.... product 1 returns 3 results ($1.00,$2.00,$3.00) for which the average is 2 (6/3 = 2) product 2 returns 4 results ($2.00,$3.00,$4.50,$1.50) for which the average is $2.75 (11/4 = 2.75) now, when you take the two averages $2.75 & $2.00 and divide that by 2 , you get $2.37 .. but if you add all of the products together 1+2+3+2+3+4.50+1.50 = 17 , then divide that by the amount of all products found 17/7 , you get $2.43 which is what i'm trying to do so i don't loose any money! so the code i have will work for the first instance when only 1 product is needed, but i need to modify that code to work on two strings (F2 & F5) for my 2nd product cell any and all help is greatly appriceated! Thanks, Brandon Roland |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
another excel question, thanks guys!
BJ it didnt work :(
thanks though. i also changed A2+A2000 to A2:A2000 and F5 to F5, as i suspected those were typos.. and it still didnt work :( Thanks though -BR "bj" wrote: try =(SUMPRODUCT(or(A2:A2000=F2,A2+A2000=F%)*(C2:C2000 )/(COUNTIF(A2:A2000,F2)+countif(A2:A2000,F5)) "brandon roland" wrote: hello, alot of you helped me w/ my first question.. now i have another.. to recapp, i've got a code that finds the search criteria (F2) from my list of A2 through A2000 , if the string is found, it takes the value in the adjacent column (C) adds it and produces a average for all the products found with the string entered in E2 , here is that code.. =(SUMPRODUCT((A2:A2000=F2)*(C2:C2000))/COUNTIF(A2:A2000,F2)) that code works, but i want to make another code for another cell, that will find two seperate strings (F2 & F5) , add them all up, and give me a average for two different products... at first i tried simply using this code twice, then adding both averages and dividing by two but i loose $ w/ that formula.. for instance.... product 1 returns 3 results ($1.00,$2.00,$3.00) for which the average is 2 (6/3 = 2) product 2 returns 4 results ($2.00,$3.00,$4.50,$1.50) for which the average is $2.75 (11/4 = 2.75) now, when you take the two averages $2.75 & $2.00 and divide that by 2 , you get $2.37 .. but if you add all of the products together 1+2+3+2+3+4.50+1.50 = 17 , then divide that by the amount of all products found 17/7 , you get $2.43 which is what i'm trying to do so i don't loose any money! so the code i have will work for the first instance when only 1 product is needed, but i need to modify that code to work on two strings (F2 & F5) for my 2nd product cell any and all help is greatly appriceated! Thanks, Brandon Roland |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
another excel question, thanks guys!
I wonder if I was trying to set a new record in typos (Not even close)
I also left off a close paren before the divide symbol try =(SUMPRODUCT(or(A2:A2000=F2,A2:A2000=F5)*(C2:C2000 ))/(COUNTIF(A2:A2000,F2)+countif(A2:A2000,F5)) "brandon roland" wrote: BJ it didnt work :( thanks though. i also changed A2+A2000 to A2:A2000 and F5 to F5, as i suspected those were typos.. and it still didnt work :( Thanks though -BR "bj" wrote: try =(SUMPRODUCT(or(A2:A2000=F2,A2+A2000=F%)*(C2:C2000 )/(COUNTIF(A2:A2000,F2)+countif(A2:A2000,F5)) "brandon roland" wrote: hello, alot of you helped me w/ my first question.. now i have another.. to recapp, i've got a code that finds the search criteria (F2) from my list of A2 through A2000 , if the string is found, it takes the value in the adjacent column (C) adds it and produces a average for all the products found with the string entered in E2 , here is that code.. =(SUMPRODUCT((A2:A2000=F2)*(C2:C2000))/COUNTIF(A2:A2000,F2)) that code works, but i want to make another code for another cell, that will find two seperate strings (F2 & F5) , add them all up, and give me a average for two different products... at first i tried simply using this code twice, then adding both averages and dividing by two but i loose $ w/ that formula.. for instance.... product 1 returns 3 results ($1.00,$2.00,$3.00) for which the average is 2 (6/3 = 2) product 2 returns 4 results ($2.00,$3.00,$4.50,$1.50) for which the average is $2.75 (11/4 = 2.75) now, when you take the two averages $2.75 & $2.00 and divide that by 2 , you get $2.37 .. but if you add all of the products together 1+2+3+2+3+4.50+1.50 = 17 , then divide that by the amount of all products found 17/7 , you get $2.43 which is what i'm trying to do so i don't loose any money! so the code i have will work for the first instance when only 1 product is needed, but i need to modify that code to work on two strings (F2 & F5) for my 2nd product cell any and all help is greatly appriceated! Thanks, Brandon Roland |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a function guys! | Excel Worksheet Functions | |||
need help guys | Excel Worksheet Functions | |||
First post, need help guys! | Charts and Charting in Excel | |||
Please Help Guys! | Excel Worksheet Functions | |||
Please Help Guys! | Excel Worksheet Functions |