Let me try this again

G

Guest

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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top