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

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

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

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

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

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

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

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

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

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

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

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!


"FSt1" wrote:

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