Hi Graham:

First, re-consider your definition of gap. The posting indicated the row
difference plus one. If the value appears in two consecutive rows, that
would say the gap is two. Perhaps you meant minus one ( if the gap
represents the number of €œempty€� cells between €œfilled€� cells)

Suppose B1 thru B20 contains:

8329.0
1099.6
7823.4
3691.1
2699.5
3721.7
9334.5
7568.0
7290.4
6878.9
1665.1
5981.7
1297.3
9236.6
7518.2
2834.5
5186.4
4901.1
6344.4
2162.5

It really does not matter how many digits or decimal places there are.

In C1 enter:
=IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down

In D2 enter:
=IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down

Leave D1 empty. Make the -1 into +1 if you still want your definition of
€œgap€�
Here is B1 thru D20:

8329.0 1
1099.6
7823.4 3 1
3691.1
2699.5 5 1
3721.7 6 0
9334.5
7568.0
7290.4 9 2
6878.9
1665.1
5981.7
1297.3 13 3
9236.6 14 0
7518.2 15 0
2834.5 16 0
5186.4
4901.1
6344.4
2162.5 20 3

The values in column C are just row numbers of the "good" cells. The values
in column D are the differences between occurances ("minus one" definition)

Finally in an un-used cell:
=MAX(D:D)

Have a pleasant day!

--
Gary''s Student - gsnu200746


"Graham" wrote:

Hi, I have a column (B) of four figure numbers, to one decimal place. I would
like to know :
1) What is the longest NON occurence of each of the digits 0-9 throughout
the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17,
and this was the longest non occurence of this number , then 2 = 17
As the column gets added to with a single entry on a daily basis, I'd then
like to know
2) How long is it since each digit 0-9 last occured.
I will want to set both up as a dynamic range, but I beleive I can do that
part!
I beleive the function FREQUENCY may do this, but I'm not sure how to set it
up to read the decimal place only. The other values are irrelevant. I beleive
from previous advice that ".0" may be particularly difficult and may have to
be handled differently to 1-9 ?
Any assistance much appreciated.