Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retun greatest # of cells between nonzero
OK, I think I've seen this before, but I can't recall how it's done. So, if I
have a table with dates as headers, and a particular row may have, say: 0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1 As individual values. I would like a formula that tells me that the longest time frame between non-zero fields was 6 days. The actual value of the nonzero cells can vary. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retun greatest # of cells between nonzero
Lets say your data is in row #1. In A2 enter:
=IF(A1=0,1,0) In B2 enter: =IF(B1=0,A2+1,0) and copy across. We see: 0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1 1 2 3 0 1 2 3 4 5 6 0 1 2 0 1 2 3 0 1 2 3 4 5 0 Now all we need is: =MAX(2:2) -- Gary''s Student - gsnu200772 "Sean Timmons" wrote: OK, I think I've seen this before, but I can't recall how it's done. So, if I have a table with dates as headers, and a particular row may have, say: 0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1 As individual values. I would like a formula that tells me that the longest time frame between non-zero fields was 6 days. The actual value of the nonzero cells can vary. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retun greatest # of cells between nonzero
That's waht I was looking for! Thank you!
"Gary''s Student" wrote: Lets say your data is in row #1. In A2 enter: =IF(A1=0,1,0) In B2 enter: =IF(B1=0,A2+1,0) and copy across. We see: 0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1 1 2 3 0 1 2 3 4 5 6 0 1 2 0 1 2 3 0 1 2 3 4 5 0 Now all we need is: =MAX(2:2) -- Gary''s Student - gsnu200772 "Sean Timmons" wrote: OK, I think I've seen this before, but I can't recall how it's done. So, if I have a table with dates as headers, and a particular row may have, say: 0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1 As individual values. I would like a formula that tells me that the longest time frame between non-zero fields was 6 days. The actual value of the nonzero cells can vary. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retun greatest # of cells between nonzero
Try this array formula** :
=MAX(FREQUENCY(IF(A2:X2=0,COLUMN(A2:X2)),IF(A2:X2< 0,COLUMN(A2:X2)))) Note that empty cells will be evaluated as 0. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sean Timmons" wrote in message ... OK, I think I've seen this before, but I can't recall how it's done. So, if I have a table with dates as headers, and a particular row may have, say: 0 0 0 1 0 0 0 0 0 0 2 0 0 1 0 0 0 4 0 0 0 0 0 1 As individual values. I would like a formula that tells me that the longest time frame between non-zero fields was 6 days. The actual value of the nonzero cells can vary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find the first nonzero cell in a row | Excel Worksheet Functions | |||
Counting NONZERO Cells - Filtered and Pivoted | Excel Discussion (Misc queries) | |||
How do I create a formula to retun a value of 0 (zero) when a VLO. | Excel Worksheet Functions | |||
find first nonzero value in column | Excel Worksheet Functions | |||
nonzero value | Excel Worksheet Functions |