Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Work Around for Nested If

  1. #1
    Guest

    Default

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

    Default

    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

  3. #3
    Guest

    Default

    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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    Guest

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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