Descending Numbers

S

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
 
R

Ron Rosenfeld

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.
Click to expand...

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)
 
S

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)
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...


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.
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
Click to expand...

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.
 
S

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.
Click to expand...



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
 
R

Ron Rosenfeld

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
Click to expand...

That list includes:
76
Click to expand...
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
Click to expand...

If that is the case, then why isn't 2P476 conditionally formatted?
 
R

Ron Rosenfeld

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
Click to expand...
....Snipped

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


The following single digits would also qualify

6
5
4
3
2
1
Click to expand...

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.
 
S

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.
Click to expand...

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
 
R

Ron Rosenfeld

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.......
Click to expand...

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

Sam Harman

But what does the cell entry look like if the horse has run only once?
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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.
 
S

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.
Click to expand...


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
 
R

Ron Rosenfeld

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
Click to expand...

Those two digits are NOT in "descending order" as you specified previously was a requirement.
It also doesnt highlight if the form is

F6
F5
F4
F3
F2
F1
Click to expand...

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.
.....but wrongly highlights the horses with the following form figures

10
20
30
40
50
60
Click to expand...

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.
 
S

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.
Click to expand...


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
 
R

Ron Rosenfeld

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
Click to expand...

Probably.

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

Ron Rosenfeld

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
Click to expand...

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"))
 
S

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?
Click to expand...

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
 
S

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"))
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

Sam,

Many of the entries in your example of a filtered column do not meet the criteria you have previously set forth.

But to look for races that have only a single line showing, add a fourth column. Label it. (e.g. Count). Then:

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


Now Filter both on the Yellow color, and also on the Count = 1
 
S

Sam Harman

=COUNTIF($B:$B,$B2)
Click to expand...


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

After I reset my Games they get scambled. Conditional Formatting orMacro messed up somewhere 1
Conditional Formatting to Compare Two Sheets of Data 1
conditional formatting, filtering then copying to another sheet 0
Array formula difficulty 10
delete rows between tow inputs numbers 3
How to vary shading of groups of rows, based on change in value in aparticular column 9
Is this possible with excel? 10
Typing "12345" and have it automaticly formated to "123.45"? 5

Top