please help me w/ changing my formula

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

=(SUMPRODUCT((A2:A2000=CHOOSE({1,2},F2,F5))*(C2:C2000)))/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.