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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 05:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com