Formula Questions

[ststern45]

Hi everyone,

Formula 1 Question:

I have a cell range A1 through E1

A1 = 01
B1 = 03
C1 = 22
D1 = 35
E1 = 36

Taking the second digits from 01,03,22,35,36 = 1, 3, 2, 5, 6

I would like to calculate how many second digits went from low to high from cell range A1 through E1
.
In the above example the answer would be 3

1 to 3 = yes
3 to 2 = no
2 to 5 = yes
5 to 6 = yes

Formula 2 Question: similar to question 1

I would like to calculate how many second digits went from high to low from cell range A1 through E1?

In the above example the answer would be 1

1 to 3 = no
3 to 2 = yes
2 to 5 = no
5 to 6 = no

Formula 3 Question:

I would like to calculate how many second digits from left to right from cell range A1 through E1 are consecutive ?

0 to 1
1 to 2
2 to 3
3 to 4
4 to 5
5 to 6
6 to 7
7 to 8
8 to 9

For example:

01-03-22-35-36 = 1 Consecutive 35-36 (5 to 6)
03-18-21-28-31 = 0 No Consecutive
12-14-23-24-31 = 1 Consecutive 23-24 (3 to 4)

Thanks in advance!!!

 

[MrKowz]

This what you need?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Numbers</td><td style=";">Low to High</td><td style=";">High to Low</td><td style=";">Consecutive</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01</td><td style=";">-</td><td style=";">-</td><td style=";">-</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">03</td><td style=";">Yes</td><td style=";">No</td><td style=";">No Consecutive</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">22</td><td style=";">No</td><td style=";">Yes</td><td style=";">No Consecutive</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">35</td><td style=";">Yes</td><td style=";">No</td><td style=";">No Consecutive</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">36</td><td style=";">Yes</td><td style=";">No</td><td style=";">Consecutive</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">VALUE(<font color="Red">RIGHT(<font color="Green">A3,1</font>)</font>)>VALUE(<font color="Red">RIGHT(<font color="Green">A2,1</font>)</font>),"Yes","No"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=IF(<font color="Blue">VALUE(<font color="Red">RIGHT(<font color="Green">A3,1</font>)</font>)<VALUE(<font color="Red">RIGHT(<font color="Green">A2,1</font>)</font>),"Yes","No"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=IF(<font color="Blue">VALUE(<font color="Red">RIGHT(<font color="Green">A3,1</font>)</font>)-1=VALUE(<font color="Red">RIGHT(<font color="Green">A2,1</font>)</font>),"Consecutive","No Consecutive"</font>)</td></tr></tbody></table></td></tr></table><br />

 

[ststern45]

That will do it!!

Thanks Mr. K.

Very much appreciated.