Eliminating particular numbers from a column help!

[Katie]

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

 

[Joe User]

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