S
Samuel
Hiya,
I am currently trying to calculate the largest gap in a series of numbers
where a particular number has not appeared and display this number in a cell.
For example,
There are 2 numbers involved, 0 and 1. My spreadsheet looks like:
A1
A2 1
A3 0
A4 0
A5 0
A6 0
A7 1
A8 0
A9 0 and so on.....
Now, in cell C1 I want excel to automatically calculate the number of times
0 has come up consecutively up to the last time the number 1 appeared. So
when inputting data downwards in A10, A11, A12 it will be able to tell me how
many consecutive 0's there have been until the last number 1 appeared.
In the above example, if I had input the data upto A5, C1 would display the
number 3 (there have been three 0's since the last 1 came up). When A6 is
filled in with a 0, C1 would display 4, yet once A7 had been filled in the
counter would reset back to 0 in C1.
I have tried to explain this as best as I can, if you need any clarification
please ask, and thank you kindly in advance,
I am currently trying to calculate the largest gap in a series of numbers
where a particular number has not appeared and display this number in a cell.
For example,
There are 2 numbers involved, 0 and 1. My spreadsheet looks like:
A1
A2 1
A3 0
A4 0
A5 0
A6 0
A7 1
A8 0
A9 0 and so on.....
Now, in cell C1 I want excel to automatically calculate the number of times
0 has come up consecutively up to the last time the number 1 appeared. So
when inputting data downwards in A10, A11, A12 it will be able to tell me how
many consecutive 0's there have been until the last number 1 appeared.
In the above example, if I had input the data upto A5, C1 would display the
number 3 (there have been three 0's since the last 1 came up). When A6 is
filled in with a 0, C1 would display 4, yet once A7 had been filled in the
counter would reset back to 0 in C1.
I have tried to explain this as best as I can, if you need any clarification
please ask, and thank you kindly in advance,