Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
eliminating hyperlinks from a complete column in Excel | Excel Discussion (Misc queries) | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
Can I change a column of calculated numbers to absolute numbers? | Excel Discussion (Misc queries) | |||
Changing column of numbers made of formulas to just numbers | Excel Discussion (Misc queries) | |||
Excel Formula - Add column of numbers but ignore negative numbers | Excel Worksheet Functions |