Descending Numbers

[Sam Harman]

if anyone can help with these i will be ternally grateful...

I have a column which contains the following


12345
12BF1
12356
14321
45670
05F60
23871



Ok a mixture of letters an numbers.....what I would like to do is
concentrate on the last two digits from the right. so for the first
row the two digits are 45 then the second row is F1 and so on.....

I would like to have some conditional formatting that highlighted the
row if the last two digits were in dscending order and the last digit
is less than 7

for example row 2 would be highlighted as any letter followed by a
number less than 7 would meet the crieteria as would row 4 and row 7.

If not possible to do with letters and number =s is there a way to do
it with numbers only so that the conditional formatting kicks in when
the last two digits are as folows:

03
02
01
11
21
32
41
51
61


etc etc


Any help gratefully appreciated

Thanks

 

[Sam Harman]

If your data starts in A1, then a formula which will do what you wish will be:

=AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7)

Thanks Ron for taking the time to respond. I fear that I have not
explained myself properly......


If the following are in cells A1 - A7 they would be conditionally
formatted as follows:

2P474 would be conditionally formatted green fill
46535 would not be formatted
2P475 would be conditionally formatted green fill
46536 would not be formatted
2P476 would not be formatted
46537 would not be formatted
2P477 would not be formatted

only those figures that end in the following belwo would meet the
criteria to be conditionally formatted.

61
51
41
31
21
11
65
64
63
62
54
52
52
43
42
32
F6
F5
F4
F3
F2
F1
P6
P5
P4
P3
P2
P1
U6
U5
U4
U3
U2
U1
B6
B5
B4
B3
B2
B1
O6
O5
O4
O3
O2
O1

I hope that helps and thanks again for your time

Sam

 

[Sam Harman]

It is not clear what you want. My formula returns true for your initial description, and can be used as a conditionally formatting formula.

But your current description not consistent with your initial requirement statement, and is also not internally consistent.


2P476: The last two digits are in descending order and the last digit is less than 7.

So why is it not highlighted?

And your list does not clarify things. Neither 74 nor 75 are listed below, yet you have them listed above as being conditionally formatted.

You need to rethink your requirements.

Thanks again Ron for your patience. It is not that easy to explain or
I am not explainign it that well.....i think the list below would be
the full list of all the possible permutations.....that would return a
conditional format of a green fill

06
05
04
03
02
01
96
95
94
93
92
91
86
85
84
83
82
81
76
75
74
73
72
71
65
64
63
62
61
54
53
52
51
43
42
41
32
31
21
11

F6
F5
F4
F3
F2
F1
B6
B5
B4
B3
B2
B1
U6
U5
U4
U3
U2
U1

The following single digits would also qualify

6
5
4
3
2
1

Thanks again ron......

Cheers

Sam

 

[Sam Harman]

...Snipped

What is the rule here? Is 11 a special case? Obviously it the two digits are not in "Descending Order"


Does that mean you might have "strings" which consist of only a single digit? You did not provide any examples of that.

?????

If there are a number of exceptions to your initially, and clearly stated rule, as you seem to be writing, perhaps the simplest thing for you would be to set up a list of allowable terminations, and use vlookup for your conditional formatting.

Ron, I did say that I was not explainign myself well enough and for
that I apologise as I can see that you are really trying to help me
here...perhaps if I explain the use of the figures it will
help.......I am carrying out some research on the results of horse
races and in particular whether there is any trend in winning
performance which relates to previous performance.

The information as shown (and the one that ended in 76 was an over
sight as that would count) in the last two digits (or1 digit if the
horse has only run once) would equate to its last two/one runs......

Horses that have progressive form I would like to identify quickly as
I have over 14,000 records to analyse and therefore being able to
conditionally format the cell when I enter the data then filter on the
cell colour would be a godsend..........

So any horse that has run one and had a form figure of 6, 5, 4, 3, 2,
or 1 would qualify for conditional formatting and any horse where its
last two runs (shown as the last two figures to the right if more than
two runs) showed progressive form would also qualify as in the long
list I previously provided.....65, 43, 21, etc.

Horses that have their last two form figures as 11 are clearly in good
heart and would qualify as they cannot be any more progressive than
11.

I hope that helps Ron and if you in any way can assist me it will be
very much appreciated.......


Thanks in advance

Sam

 

[Sam Harman]

But what does the cell entry look like if the horse has run only once?

Hi Ron, the following cells relate to the form cells for an 8 runner
race.


1
122
21
41
832
4457
35
49

All cells are formatted as General as they can contain numbers and
letters i.e 3F213 or 3U232 (U is where the horse unseated the rider, F
when the horse fell in the race, B means the horse was brought down by
another horse.

Thanks again

Sam

 

[Sam Harman]

Well, if you've described the variations, the following formula should cover it:

=OR(AND($A1>=1,$A1<=6),
--RIGHT($A1,2)=11,
AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7))

Use that in the conditional formatting dialog, changing $A1 to the appropriate first cell of your column of form cells, using the same form of mixed addressing.

Ron, thanks very much it is almost perfect but I have the following
problems.............


If a horse has 2 runs or more and its form figures end in 06, 05, 04,
03, 02, 01 it does not highlight the cell

It also doesnt highlight if the form is

F6
F5
F4
F3
F2
F1
B6
B5
B4
B3
B2
B1
U6
U5
U4
U3
U2
U1

......but wrongly highlights the horses with the following form figures

10
20
30
40
50
60


Thanks for any further help on this.....at the very least i am much
further forward than I was

Cheers

Sam

 

[Sam Harman]

Those two digits are NOT in "descending order" as you specified previously was a requirement.


That is due to an error in my formula. If you change it to:

=OR(AND($A1>=1,$A1<=6),
RIGHT($A1,2)="11",
AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),--RIGHT($A1,1)<7))

it will correctly highlight those endings.


Those two digits ARE in descending order and the last digit is less than 7, meeting your specifications.

Again, you still need to fully state your specifications in a clear and unambiguous way.

Ron, you have the patience of a saint

Yes they are in descending order, what I didn't make clear (again) was
that 0 in horse race terms is a horse that ran 10th or more in the
race, hence the 0.

Is there any way that can be incorporated into the formula so that 01,
02, 03, 04, 05 and 06 are counted as descending and 10, 20, 30, 40,
50, 60 are counted as ascending?

Thanks again

Sam

 

[Sam Harman]

Probably.

Answer this: Can a form cell contain ONLY a single digit, or will there ALWAYS be at least two characters in the cell?

Ron, a form cell can contain a single digit such as 1, 2, 3, 4, 5, 6,
7, 8, 9, 0, or indeed a single letter such as F (Fell), B (Brought
Down), U (Unseated Rider)

Cheers

Sam

 

[Sam Harman]

Try this:

=OR(AND($A1>=1,$A1<=6),
RIGHT($A1,2)="11",
AND(CODE(RIGHT($A1,2))=48,RIGHT($A1,1)<"7",RIGHT($A1,1)>"0"),
AND(CODE(RIGHT($A1,2))>CODE(RIGHT($A1,1)),RIGHT($A1,1)<"7",RIGHT($A1,1)>"0"))

Ron, I think you may have nailed it..i haven't tested it fully as it
is very late but will feedback to you if there any problems althought
at first glance it looks great - thank you so much.....

Can I be cheeky and ask you another quick question?

Say for example I have 40 races all going off at different times and
therefore in colum B I have the race times formatted as time as
follows:

Date Time Form

24-Jun 18:40:00 2
24-Jun 18:40:00 2
24-Jun 18:40:00 6
24-Jun 18:40:00 632
24-Jun 18:40:00 none
24-Jun 18:40:00 none
24-Jun 18:40:00 30
24-Jun 19:15:00 41542
24-Jun 19:15:00 78161
24-Jun 19:15:00 36888
24-Jun 19:15:00 23343
24-Jun 19:15:00 29324
24-Jun 19:15:00 53282
24-Jun 19:15:00 31357
24-Jun 19:15:00 71608
24-Jun 19:15:00 835
24-Jun 19:15:00 52447
24-Jun 19:15:00 48145
24-Jun 19:15:00 878
24-Jun 19:15:00 906
24-Jun 19:15:00 82400
24-Jun 19:45:00 94211
24-Jun 19:45:00 11001
24-Jun 19:45:00 17131
24-Jun 19:45:00 62713
24-Jun 19:45:00 15075
24-Jun 19:45:00 16323
24-Jun 19:45:00 37433
24-Jun 20:15:00 600
24-Jun 20:15:00 49237
24-Jun 20:15:00 39218
24-Jun 20:15:00 20084
24-Jun 20:15:00 8008
24-Jun 20:15:00 4208
24-Jun 20:15:00 23567
24-Jun 20:15:00 50004
24-Jun 20:15:00 80008
24-Jun 20:15:00 44432
24-Jun 20:50:00 12
24-Jun 20:50:00 9621
24-Jun 20:50:00 53472
24-Jun 20:50:00 43208
24-Jun 20:50:00 66312
24-Jun 20:50:00 33148
24-Jun 20:50:00 62245
24-Jun 20:50:00 953
24-Jun 21:20:00 22406
24-Jun 21:20:00 92444
24-Jun 21:20:00 90857
24-Jun 21:20:00 78268
24-Jun 21:20:00 60573
24-Jun 21:20:00 74109
24-Jun 21:20:00 14470
24-Jun 21:20:00 0U941
24-Jun 21:20:00 09P60
24-Jun 21:20:00 12402
24-Jun 21:20:00 660P8
24-Jun 21:20:00 51462
24-Jun 21:20:00 96415
24-Jun 21:20:00 1206
24-Jun 21:20:00 44458
24-Jun 21:20:00 76825
24-Jun 21:20:00 29887


Now suppose I filter the form column to only show those that have
progressive form. i.e those cells filled yellow as per the conditional
formatting.......and my list then becomes like below

24-Jun 19:15:00 53282
24-Jun 19:45:00 94211
24-Jun 19:45:00 11001
24-Jun 19:45:00 17131
24-Jun 19:45:00 62713
24-Jun 19:45:00 15075
24-Jun 19:45:00 16323
24-Jun 19:45:00 37433
24-Jun 20:15:00 20084
24-Jun 20:50:00 12
24-Jun 20:50:00 9621
24-Jun 20:50:00 53472
24-Jun 20:50:00 43208
24-Jun 20:50:00 66312
24-Jun 20:50:00 33148
24-Jun 20:50:00 62245
24-Jun 20:50:00 953
24-Jun 21:20:00 22406
24-Jun 21:20:00 92444
24-Jun 21:20:00 51462
24-Jun 21:20:00 96415
24-Jun 21:20:00 1206
24-Jun 21:20:00 44458
24-Jun 21:20:00 76825
24-Jun 21:20:00 29887

Imagine if you can 200 rows in the time column where some have one
time entry and others have more than one......

Is there any way I can filter the time column to only show those race
times whn only one horse has progressive form

So if I were to filter the time column as per the example above the
19:15 race and the 20:15 race would be the only remaining records.


Thanks very much again Ron


Cheers

Sam

 

[Sam Harman]

=COUNTIF($B:$B,$B2)

Thanks Ron, sorry but the list was illustrative of the single entry in
the time field and therefore the entries in the form field were not
all yellow.....

I have added the formula in as you suggest but instead of getting a
count of 1, 2,3 etc i get the following

Date Time Count
25-Jun 13:45:00 105
25-Jun 13:45:00 105
25-Jun 13:45:00 105
25-Jun 14:00:00 284
25-Jun 14:00:00 284
25-Jun 14:05:00 97
25-Jun 14:05:00 97
25-Jun 14:05:00 97


Isa this because it is counting the time field which is formatted in
hh:mm:ss ?

Thanks again Ron

Sam

 

[Sam Harman]

Sam,

It does count the time field (that is the Column B in the formula). From your data, that would seem to be the easiest way to determine that there is only a single horse in the list. But it is not checking the date.

Does your data have entries for more than one date?

If so, try this formula instead:

=COUNTIFS($B:$B,B2,$A:$A,A2)

Note: I am assuming, since you can filter by color, that you have Excel 2007 or later. Earlier versions do not have this function and we'd need a different formula.

Since your data has entries from multiple tracks, I'm surprised you don't have an identifying column in your data to indicate which track the data refers to. Obviously, if you are interested in whether a given race has only a single "hit", unless races at different tracks never have the same time, there's no way to do that.

Hi Ron, the new formula correctly counts the number of entries for
each of the time slots but when filter the number stays the same. See
below:

Before filtering on the form column for yellow



25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8
25-Jun 14:00:00 8

After filtering the form colum for yellow progressive horses

25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 13:45:00 16
25-Jun 14:00:00 8
25-Jun 14:00:00 8


Thanks

Sam

 

[Sam Harman]

OK so I guess it doesn't matter that you might have multiple races at different tracks starting at the same time.

COUNTIFS is not sensitive to filtered rows. You have to use a different method of getting at it.

Instead of the countifs formula, try this:

D2:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1,ROW(INDIRECT("1:"&-1+COUNTA($A:$A))),0,1))*
(A2=OFFSET($A$1,1,0,-1+COUNTA($A:$A),1))*(B2=OFFSET($B$1,1,0,-1+COUNTA($B:$B),1)))

It is CRITICAL that there be no blank lines in the data range.

After you filter by color, select in the filter to retun only 1

Hi Ron, I have been on goliday for the last three weeks so this is the
first opportunity I have had to thank you so much for all the help you
have given me with my spreadsheet. I still have a few more niggling
questions which I hope there is a solution to but I will give you a
break before I ask if thats alright.

Thanks again you have been a fantastic help

Cheers

Sam