|
Finding lowest count in any column
Hi, group,
I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF(D :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0"))+ 1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah |
Finding lowest count in any column
Can you use
=MIN(E2:I2) This will ignore Text and cells without an entry edvwvw Sarah H. wrote: Hi, group, I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF( D:D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUN TIF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0")) +1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah -- Message posted via http://www.officekb.com |
Finding lowest count in any column
Actually, the way you're doing it is pretty good although the formula is
sort of long. A shorter formula but not necessarily better since it's volatile: Array entered** : =MIN(COUNTIF(OFFSET(B2:G10,,COLUMN(B2:G10)-COLUMN(B2),,1),"0")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Another option and probably the best approach is to use a separate COUNTIF on each column then get the MIN from that range. For example: On row 11 starting in B11 and copied across to G11: =COUNTIF(B2:B10,"0") Then: =MIN(B11:G11)+1 -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... Hi, group, I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF(D :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0"))+ 1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah |
Finding lowest count in any column
Interesting, Don! Thank you very much for the ideas. I will play with
this. -- Sarah "Don Guillett" wrote in message ... How about a nice macro Sub lowestcolumncount() minnum = 20 mycol = 10 For i = 2 To 10 mc = Application.CountIf(Columns(i), "0") 'MsgBox mc If mc < minnum Then minnum = mc mycol = i End If 'MsgBox minnum Next i MsgBox "Column " & mycol & " " _ & " Min count=" & minnum End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Sarah H." wrote in message ... Hi, group, I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF(D :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0"))+ 1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah |
Finding lowest count in any column
"edvwvw" or "Uwe,"
While that doesn't answer the immediate question, it does have use to me in constructing a helper column. I had been using an OR statement for that. So thanks! -- Sarah "edvwvw via OfficeKB.com" <u42512@uwe wrote in message news:9643764bd3b3d@uwe... Can you use =MIN(E2:I2) This will ignore Text and cells without an entry edvwvw Sarah H. wrote: Hi, group, I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF (D:D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COU NTIF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0") )+1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah -- Message posted via http://www.officekb.com |
Finding lowest count in any column
Biff,
That's kind of cool! Thank you. I am trying to figure out how it works now. I have taken your suggestion that I simplify with a helper-column under advisement as well. :-) -- Sarah "T. Valko" wrote in message ... Actually, the way you're doing it is pretty good although the formula is sort of long. A shorter formula but not necessarily better since it's volatile: Array entered** : =MIN(COUNTIF(OFFSET(B2:G10,,COLUMN(B2:G10)-COLUMN(B2),,1),"0")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Another option and probably the best approach is to use a separate COUNTIF on each column then get the MIN from that range. For example: On row 11 starting in B11 and copied across to G11: =COUNTIF(B2:B10,"0") Then: =MIN(B11:G11)+1 -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... Hi, group, I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF(D :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0"))+ 1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah |
Finding lowest count in any column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sarah H." wrote in message ... Biff, That's kind of cool! Thank you. I am trying to figure out how it works now. I have taken your suggestion that I simplify with a helper-column under advisement as well. :-) -- Sarah "T. Valko" wrote in message ... Actually, the way you're doing it is pretty good although the formula is sort of long. A shorter formula but not necessarily better since it's volatile: Array entered** : =MIN(COUNTIF(OFFSET(B2:G10,,COLUMN(B2:G10)-COLUMN(B2),,1),"0")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Another option and probably the best approach is to use a separate COUNTIF on each column then get the MIN from that range. For example: On row 11 starting in B11 and copied across to G11: =COUNTIF(B2:B10,"0") Then: =MIN(B11:G11)+1 -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... Hi, group, I'm interested in knowing the lowest count of non-zero numbers in any column on my sheet. Columns are from B through I. Actually, I want to add 1 to the low count - because of the header row. Using ordinary functions I did it this way: =MIN(COUNTIF(B:B,"0"),COUNTIF(C:C,"0"),COUNTIF(D :D,"0"),COUNTIF(E:E,"0"),COUNTIF(F:F,"0"),COUNT IF(G:G,"0"),COUNTIF(H:H,"0"),COUNTIF(I:I,"0"))+ 1 The columns look like this: 21-Nov-08 43.44% 159.59% 12.32% 242.42% 76.30% 112.34% 27.79% 219.12% 20-Nov-08 42.15% 167.40% 11.57% 260.63% 69.85% 127.98% 24.91% 248.27% 19-Nov-08 47.22% - 14.33% 221.28% 100.00% 100.00% 29.06% 219.12% 18-Nov-08 52.74% - 18.36% 182.31% - - 32.28% 197.03% 17-Nov-08 53.52% - 18.74% 179.29% - - 32.04% 200.40% 14-Nov-08 57.16% - 20.74% 163.84% - - 33.32% 191.11% (The dashes are zero-values.) Column C above has 2 non-zero values. It's the lowest columnar count across the sheet. That's what I want to know. So my question is: is there a better, as in more efficient, way to do this? For example, I could search for the row number of the first zero-value using MATCH. I don't know if that would be faster or better, but my instinct says it would be slower. But maybe some cross-range way would be better rather than doing each column at a time. Thanks for any ideas! Sarah |
| All times are GMT +1. The time now is 03:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com