Eliminating particular numbers from a column help!
I have a column of subtotals, and need to eliminate all cells were the
subtotal is 1, and then calculate the mean and SD of the remaining subtotals left in the column. What is the best way to go about setting this up in a spreadsheet and what formula do I use to get rid of the 1's? Cheers, Katie |
Eliminating particular numbers from a column help!
"Katie" wrote:
what formula do I use to get rid of the 1's? Do you really want to "get rid" of the 1s literally in the subtotals column -- i.e, they should not appear in the column -- or is it sufficient to __exclude__ them from the mean and sd computation? If the latter, then try the following array formulas: =AVERAGE(IF(A1:A20<1, A1:A20)) =STDEVP(IF(A1:A20<1, A1:A20)) Note that an array formula is entered by pressing ctrl+shift+Enter instead of just Enter. You should then see curly braces around the entire formula, e.g. {=formula}. If you make a mistake, you can edit the cell by pressing F2, then press ctrl+shift+Enter. ----- original message ----- "Katie" wrote in message ... I have a column of subtotals, and need to eliminate all cells were the subtotal is 1, and then calculate the mean and SD of the remaining subtotals left in the column. What is the best way to go about setting this up in a spreadsheet and what formula do I use to get rid of the 1's? Cheers, Katie |
All times are GMT +1. The time now is 10:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com