| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding index | Excel Worksheet Functions | |||
| Copying cells based on a criterion | Excel Discussion (Misc queries) | |||
| Finding the median of numbers meeting criteria | Excel Discussion (Misc queries) | |||
| Number of items meeting a > criterion | Excel Worksheet Functions | |||
| finding the row index for the last value > than some value | Excel Worksheet Functions | |||
Linear Mode
