Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help me w/ changing my formula
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 modify the 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.programming
|
|||
|
|||
please help me w/ changing my formula
=ROUND(AVERAGE(IF(((A2:A20=F2)+(A2:A20=F5)),C2:C20 )),2)
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "brandon roland" wrote in message ... 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 modify the 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.programming
|
|||
|
|||
please help me w/ changing my formula
=(SUMPRODUCT((A2:A2000=CHOOSE({1,2},F2,F5))*(C2:C2 000)))/SUM(COUNTIF(A2:A2000,F2),COUNTIF(A2:A2000,F5))
or =SUM(SUMIF(A2:A2000,F2,C2:C2000)+SUMIF(A2:A2000,F5 ,C2:C2000))/SUM(COUNTIF(A2:A2000,F2),COUNTIF(A2:A2000,F5)) Both worked for me. I wouild expect the second one to be more efficient. -- Regards, Tom Ogilvy "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 modify the 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 | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
how to edit formula without changing formula of each cell | Excel Worksheet Functions | |||
Changing formula to a value | Excel Worksheet Functions | |||
Copy Formula Down Without Changing Entire Formula | Excel Discussion (Misc queries) | |||
Formula changing | Excel Discussion (Misc queries) |