Frequency of numbers in a range

[Graham]

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.

 

[Gary''s Student]

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)

Have a pleasant day!

 

[Graham]

Hi GS, Many thanks for your reply. I often confuse myself on the "gap". In
this case I would want to count the longest occurence of "4" as being 8. i.e.
from B9:B17.(Using your data column) In the case of the same digit appearing
consecutively, I would want to count this as 1.
I'll experiment to see which gives me the correct outcome.
I had hoped to create a table along the lines of:

X Y Z
0 12 12
1 6 2
2 5 5
3 7 7
4 6 1
5 9 0
6 10 6
7 8 8
8 20 20
9 10 10

Where column X is the digit, Column Y is the longest non-occurence, or gap,
and Column Z is the gap since it's most recent occurence. (Obviously as this
is a short column of data, Y & Z are often the same value!)

I will work with your suggestions and see where i get with it.
Regards
Graham

 

[Graham]

My apologies, having worked through your solution, I can see that I didn't
make myself very clear. I'm only interested in the occurence of numbers in
the first decimal place position. It is the numbers to the left of the
decimal place that are irrelevant.

 

[Gary''s Student]

Apologies not neceassary.

I will update this posting tomorrow to include only the digit to the
immediate right of the decimal point.

 

[Gary''s Student]

Hi Graham: Similar to before except:

In C1 enter:
=IF(ISERROR(SEARCH("2",RIGHT(TEXT(B1,"0.0"),1))),"",ROW())
only look for that single digit to the right of the decimal point


In D2 enter:
=IF(C2="","",C2-MAX($C$1:C1))
to correct the definition of gap

 

[Graham]

Once again many thanks. Simply changing the "2" to "*.2" also apears to work.
I've copied this across for each of the digits and seems to work for all,
including "0".
It doesn't pick up if the longest gap is actually at the end of the data,
but I should now be able to use the columns to find the latest occurence,
compare the two, and show the longer, which should overcome that issue.
Unless of course you have a more refined way of doing so ?
Regards
Graham