Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: If / then function

  1. #1
    Guest

    Default

    I have been trying to enter a funtion into a cell to check that if that particular cell,from sheet 1, contains a lower total (number) than each of 4 to 5 totals in other cells on sheet 1, then the amount from another cell on sheet 1 is to be reflected in a cell on sheet 2. I'll reference cells in order to give you an idea of what I'm trying to do.
    If (sheet1)A6

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-13 10:32, Anonymous wrote:
    I have been trying to enter a funtion into a cell to check that if that particular cell,from sheet 1, contains a lower total (number) than each of 4 to 5 totals in other cells on sheet 1, then the amount from another cell on sheet 1 is to be reflected in a cell on sheet 2. I'll reference cells in order to give you an idea of what I'm trying to do.
    If (sheet1)A6 < B6,C6,D6,E6 AND F6, then reflect the number contained in (sheet 1) A2 in (sheet 2) A2. Using multiple sheets and cells, I just can't get this formula to work. Any help would be great. Thanks
    Must A6 be less than each of B6, C6, E6, and F6? If so,

    in A2 in Sheet2 enter:

    =(SUMPRODUCT((Sheet1!B6:F6>Sheet1!A6)+0)=5)*Sheet1!A2

    which is more concise than IF with a long AND.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try =(A6
    add your sheet references and check what result you want for negative test.

    The above gives value of a2 if true and
    0 if false.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-13 12:00, Dave Patton wrote:

    try =(A6 < MIN(B6:F6))*A2

    add your sheet references and check what result you want for negative test.

    The above gives value of a2 if true and
    0 if false.
    That's the right one, Dave. Guess i need more coffie.

    Watch out for the less than sign: just disable HTML.

    & glad to see you here.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-13 12:04 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    Thanks Aladin

    I wondered why the formula did not show.

    Dave Patton

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
  •