another excel question, thanks guys!

[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 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

 

[Guest]

mike H you gave me the code yesterday maybe you can help me alter it for my
2nd cell?

 

[Guest]

try
=(SUMPRODUCT(or(A2:A2000=F2,A2+A2000=F%)*(C2:C2000)/(COUNTIF(A2:A2000,F2)+countif(A2:A2000,F5))

 

[Guest]

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

 

[Guest]

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
tr
=(SUMPRODUCT(or(A2:A2000=F2,A2:A2000=F5)*(C2:C2000))/(COUNTIF(A2:A2000,F2)+countif(A2:A2000,F5))