Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode When Ignoring Zeroes
I've done some searching; I hope this hasn't been answered elsewhere.
I have a column of data where I want to calculate the modes while ignoring zeroes that are in the table. So: Column 1 1 1 1 0 0 0 0 2 3 Mode w/o Zeroes = 1 Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode When Ignoring Zeroes
Try the following array formula:
=MODE(IF(A1:A10<0,A1:A10,FALSE)) Since this is an array formula, you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. See http://www.cpearson.com/excel/ArrayFormulas.aspx for much more information about array formulas. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Rothman" wrote in message ... I've done some searching; I hope this hasn't been answered elsewhere. I have a column of data where I want to calculate the modes while ignoring zeroes that are in the table. So: Column 1 1 1 1 0 0 0 0 2 3 Mode w/o Zeroes = 1 Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode When Ignoring Zeroes
Thanks. Smacking my head on this one; I was fiddling with a similar, flawed
formula; now I'm straightened out. "Chip Pearson" wrote: Try the following array formula: =MODE(IF(A1:A10<0,A1:A10,FALSE)) Since this is an array formula, you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. See http://www.cpearson.com/excel/ArrayFormulas.aspx for much more information about array formulas. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Rothman" wrote in message ... I've done some searching; I hope this hasn't been answered elsewhere. I have a column of data where I want to calculate the modes while ignoring zeroes that are in the table. So: Column 1 1 1 1 0 0 0 0 2 3 Mode w/o Zeroes = 1 Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mode When Ignoring Zeroes
=MODE(IF(A1:A9<0,A1:A9,"")) entered as an array formula
(Control-Shift-Enter) -- David Biddulph "Rothman" wrote in message ... I've done some searching; I hope this hasn't been answered elsewhere. I have a column of data where I want to calculate the modes while ignoring zeroes that are in the table. So: Column 1 1 1 1 0 0 0 0 2 3 Mode w/o Zeroes = 1 Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
F5 function for zeroes? | Excel Worksheet Functions | |||
Combo Box goes to edit mode even if design mode is in OFF position | Excel Discussion (Misc queries) | |||
coverting answer from Radian mode to degree mode | Excel Worksheet Functions |