Let me try this again

[Guest]

I did a poor job of explaining my issue on a previous post, so let me try again...

- I've got a series of data in 9 columns.
- Any or all of the columns could be empty
- If any cells have data in them, the data will be a numberic value (could be positive or negative)
- Any data in the cells must be greater than any values in an cells to the left of them.

Examples:
This data would be OK.
A1=1
B1=2
C1=3
D1=4
E1=5
F1=6
G1=7
H1=8
I1=9

This data would also be OK (note that I've used "null" to indicate an empty cell
A1=1
B1=null
C1=null
D1=null
E1=null
F1=2
G1=null
H1=null
I1=9

This data would not be OK because the data in A1 is greater than the data in F1.
A1=1
B1=null
C1=null
D1=null
E1=null
F1=0
G1=6
H1=9
I1=11

This data would not be OK because the data in B1 equals A1 (it needs to be greater than, not greater than or equal to).

A1=1
B1=1
C1=3
D1=4
E1=5
F1=6
G1=7
H1=8
I1=9

Any help would be appreciated.

Scot Jonas
jonas.sb@pg.com

 

[lenze]

You can use Data validation if the data is being entered from the keyboard

Use the custom option. For example,for column D, the formula might be =D2>MAX(A2:C2). By using relative cell references, you can copy the validation down the whole column.
This message was edited by lenze on 2002-03-06 12:46

 

[Aladin Akyurek]

On 2002-03-06 12:18, Anonymous wrote:
I did a poor job of explaining my issue on a previous post, so let me try again...

- I've got a series of data in 9 columns.
- Any or all of the columns could be empty
- If any cells have data in them, the data will be a numberic value (could be positive or negative)
- Any data in the cells must be greater than any values in an cells to the left of them.

Examples:
This data would be OK.
A1=1
B1=2
C1=3
D1=4
E1=5
F1=6
G1=7
H1=8
I1=9

This data would also be OK (note that I've used "null" to indicate an empty cell
A1=1
B1=null
C1=null
D1=null
E1=null
F1=2
G1=null
H1=null
I1=9

This data would not be OK because the data in A1 is greater than the data in F1.
A1=1
B1=null
C1=null
D1=null
E1=null
F1=0
G1=6
H1=9
I1=11

This data would not be OK because the data in B1 equals A1 (it needs to be greater than, not greater than or equal to).

A1=1
B1=1
C1=3
D1=4
E1=5
F1=6
G1=7
H1=8
I1=9

Any help would be appreciated.

Scot Jonas
jonas.sb@pg.com

Scot,

What follows will enable you to check whether a given set of numeric values are in ascending order without equality:

Array-enter:

=COUNT(A1:H1)=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(A1:H1,A1:H1)),MATCH(A1:H1,A1:H1),""),IF(ISNUMBER(MATCH(A1:H1,A1:H1)),MATCH(A1:H1,A1:H1),""))>0,1))

where A1:H1 houses the set of the values of interest.

In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Note. I hope you're not going to copy this formula to a huge number of rows, otherwise it will have an adverse effect on the performance of your spreadsheet.

Aladin
This message was edited by Aladin Akyurek on 2002-03-06 13:56

 

[Aladin Akyurek]

Forgat to tell you that the array-formula returns TRUE if the set of values is OK, otherwise FALSE.
This message was edited by Aladin Akyurek on 2002-03-06 13:56
This message was edited by Aladin Akyurek on 2002-03-06 14:11

 

[Guest]

Aladin,

THANKS!!!!!

Scot