Eliminating particular numbers from a column help!

  • Thread starter Thread starter Katie
  • Start date Start date
K

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
 
Katie said:
what formula do I use to get rid of the 1's?
Click to expand...

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