Work Around for Nested If

[Guest]

I've got data in 9 columns. Any or all of the columns may be empty. However if, a column does contain a value, the value needs to be greater than the previous column that also contains a value. Example

A B C D E F G H I
1 2 3 5 This would be OK
2 4 3 This would be bad.

Any idea how to do this?

Thanks,
Scot

 

[Guest]

OK that did not show up right - let's try again.

I've got data in 9 columns. Any or all of the columns may be empty. However if, a column does contain a value, the value needs to be greater than the previous column that also contains a value. Example (note that underscores are really blank cells)

A|B|C|D|E|F|G|H|I
1|_|2|_|3|_|_|5|_ This would be OK
_|2|4|_|_|_|_|_|3 This would be bad.

Any idea how to do this?

Thanks,
Scot

 

[Guest]

BTW - the checking formula needs to be in one cell only. I can't use fomrulas in multiple cells to check subsets and then compare those results.

 

[Mark W.]

To check values in row 2 use...

{=IF(AND(OFFSET(A2,,,,COUNT(2:2)-1)<OFFSET(A2,,1,,COUNT(2:2)-1)),"OK","Not OK")}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-03-05 10:45

 

[Guest]

Mark - Thanks! This is REALLY close to what I need. I did a poor job of explaining my need. the range of 9 cells (in one row) may have a no value (not a zero) in any or all cells. So, assuming colums A:I, there might be values in B, C, E, G, and H. For another row, it could be A, E, and I. Regardless of where the blanks are, I need to compare the values across the row. So in the first example, C has to be greater than B, E has to be greater than C, etc. In the second example, I>E>A. Can this flexibility be handled?

The example you sent me assumes a value in every cell.

Again, thanks for the follow-up,
Scot

On 2002-03-05 09:10, Mark W. wrote:
To check values in row 2 use...

{=IF(AND(OFFSET(A2,,,,COUNT(2:2)-1)<OFFSET(A2,,1,,COUNT(2:2)-1)),"OK","Not OK")}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-03-05 10:45