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

regards
FSt1

"FSt1" wrote:

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


"Babymech" wrote:

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

"FSt1" wrote:

hi
try using the sumif and countif togeather ie..

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

regards
FSt1

"Babymech" wrote:

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