Work Around for Nested If

G

Guest

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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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