Finding the avg of several of cells meeting a criterion (index, ma

  • Thread starter Thread starter Babymech
  • Start date Start date
B

Babymech

I have a range of numeric values in A2:A490, and a range of text values in
B2:B490. In C2, I want to have the AVERAGE of all values in A2:A490 that
match against a specific string in B2:B490.

For example ( I hope this formating will work...):

A B C D
1 23 Buy 23.33 21
2 21 Sell
3 21 Sell
4 22 Buy
5 23 Buy

So in C1, I want to have a formula that averages all values in A for which
the corresponding cell on the same row in B contains the word "Buy". In D1 I
want to have the average of all "Sell" numbers. Any suggestions (preferrably
without going into VBA)?

PS. As an extra bonus question, is it possible to set this up so that the
criterion checked for in C1 is whether or not LEFT(B1:B5;1)="Y"? Thanks
 
hi
try using the sumif and countif togeather ie..

=SUMIF(B2:B490,"sell",A2:A490)/COUNTIF(B2:B490,"sell")

regards
FSt1
 
Almost there, but unfortunately there's one twist to it... in reality A2:A490
contain a bunch of unnecessary text, so what I really want to do is average
the first four numbers of each cell in A2:A490 that meets my criterion...
i.e. something like the following:

=SUMIF(B2:B490,"sell",LEFT(A2:A460;4))/COUNTIF(B2:B490,"sell")

But then of course Excel just tells me my formular contains an error... Any
further suggestions (and thanks a lot for the first answer, too)?
 
hi.
should have stated that first.
in a hurry right now so try this...
=LEFT(SUMIF(B1:B9,"sell",A1:A9)/COUNTIF(B1:B9,"sell"),4)
OR
=LEFT(SUMIF(B2:B9,"sell",A2:A9),4)/COUNTIF(B2:B9,"sell")
i'm thinking you might get different result if your sum total is 1000 vs.
10000 because the first 4 of a 1000 is the same as the first 4 of 10000 but i
don't have time to test right now. i'll be back in 2~3hrs. play with it.

Regards
FSt1
 
hi,
back
had time to think.
try this....
=SUMPRODUCT(LEFT(A2:A9,4)*(B2:B9="sell"))/COUNTIF(B2:B9,"sell")

regards
FSt1
 
Hmm... I just get #N/A as a result if I use that that, and I'm not skilled
enough to find out more about the error. I did manage to solve the problem in
a much less elegant way though, with the help of your earlier post - I set up
a new column that extracted the necessary data (the four characters) and then
used that column as the range, meaning that i didn't have to bother with
averaging only the first four characters. While it would be nice to have it
all in one formula, I think this will be fine.

Thanks a lot for your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top