+ Reply to Thread
Results 1 to 9 of 9

Need help comparing numbers with if function

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    Alabama
    MS-Off Ver
    MS office pro plus 2010
    Posts
    5

    Need help comparing numbers with if function

    I am trying to compare three cells containing numbers. I would like the result of the function to display text if any of the values differ from the others by + or - 20 points.

    Example:
    C1 855 This number 20 points lower than the first or second number, but could be 20 points higher.
    C2 875
    C3 875

    I have a partial function that works comparing one of the numbers.

    =IF(C1<=C2-20,"Valve Leakby",IF(C1>=C2+20,"Valve Not Open",IF(C1<>C2+20,"")))

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Need help comparing numbers with if function

    Hi powerops,

    welcome to the forum.

    For lower, you need "value leadby" and for higher, you need "value not open" .. otherwise, you could use ABS function to just make out if the difference is greater than or equal to 20.

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    01-04-2013
    Location
    Alabama
    MS-Off Ver
    MS office pro plus 2010
    Posts
    5

    Re: Need help comparing numbers with if function

    Thanks dilipandey,
    I'm not sure I am explaining the issue correctly, the numbers in column "C" will change each time the sheet is opened. The funtion I have shown does what I want comparing C1 to C2, what I am looking for is to compare each cell with the other. Any time one of the cell numbers change greater or less than 20 from the other two cells, I want the text displayed next to the cell that has the deviation.
    example.jpg
    Hope I explained that correctly and thank you for your response

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Need help comparing numbers with if function

    I want the text displayed next to the cell that has the deviation.
    but you need different text to be displayed if number is greater / and different for lower ... correct ?

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    01-04-2013
    Location
    Alabama
    MS-Off Ver
    MS office pro plus 2010
    Posts
    5

    Re: Need help comparing numbers with if function

    Yes, and the function I have does that, but only compares C1 to C2. I need each cell to compare itself to the other two and give the same text.
    I'm trying to upload the excel file.
    Attached Files Attached Files

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Need help comparing numbers with if function

    Okay.. see the attached file.. and let me know if this helps. thanks.
    Test Sheet.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Registered User
    Join Date
    01-04-2013
    Location
    Alabama
    MS-Off Ver
    MS office pro plus 2010
    Posts
    5

    Re: Need help comparing numbers with if function

    I think that will do it...Thank You so much for your patience. It's wonderful to have people like yourself on here to help those of us trying to learn.

    Thanks again and have a good Year!

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Need help comparing numbers with if function

    you are welcome Powerops...

    happy new years wishes to you too.....Cheers

    Regards,
    DILIPandey
    <click on below * if this helps>

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Need help comparing numbers with if function

    you are welcome Powerops...

    happy new years wishes to you too.....Cheers

    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1