Thanks:  0
Likes:  0

Thread: Work Around for Nested If

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

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

Any idea how to do this?

Thanks,
Scot

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

4. To check values in row 2 use...

{=IF(AND(OFFSET(A2,,,,COUNT(2:2)-1)
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 ]

5. 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)
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 ]

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•