+ Reply to Thread
Results 1 to 5 of 5

Determine a set of values to use based on another cell's data, AND returning a blank cell

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    iowa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Determine a set of values to use based on another cell's data, AND returning a blank cell

    I hope someone can help, I've been searching the internet for 3 days and I have 2 problems. Here's what I'm needing to do:

    1. In B5 I enter a number, in C5 (the Indicator) should show "LOW, "BORDERLINE LOW", "BORDERLINE HIGH", "HIGH" with the number entered. The formula that I have for this and works fine is: =IF(B5>100,"HIGH",IF(B5=100,"BORDERLINE HIGH",IF(B5=85,"BORDERLINE LOW",IF(B5<85,"LOW",))))

    The problem comes in when there is no value to enter in B5. I'd like for C5 to return a blank cell if there is no value entered in B5.

    2. In B13 I enter a number. Depending on whether G2 is an "M" or an "F" will determine which numbers need to be in C13, for example if G2 is an "F" the formula needs to look like =IF(B13>14.5,"HIGH",IF(B13=14.5,"BORDERLINE HIGH",IF(B13=13.5,"BORDERLINE LOW",IF(B13<13.5,"LOW",)))). And if G2 is an "M" then the formula needs to look like =IF(B13>15,"HIGH",IF(B13=15,"BORDERLINE HIGH",IF(B13=14,"BORDERLINE LOW",IF(B13<14,"LOW",)))). So C13 ( the Indicator) should return the appropriate value based on which set of parameters to use.

    I've tried using the AND function but never seem to get it right. For this one I also need a blank cell returned in C13 if B13 has no number entered.

    Thanks so much!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: Determine a set of values to use based on another cell's data, AND returning a blank c

    The problem comes in when there is no value to enter in B5. I'd like for C5 to return a blank cell if there is no value entered in B5.

    =IF(B5>100,"HIGH",IF(B5=100,"BORDERLINE HIGH",IF(B5=85,"BORDERLINE LOW",IF(B5<85,"LOW",))))
    =IF(B5="","",IF(B5>100,"HIGH",IF(B5=100,"BORDERLINE HIGH",IF(B5=85,"BORDERLINE LOW",IF(B5<85,"LOW",)))))

    will answer that

    for example if G2 is an "F" the formula needs to look like =IF(B13>14.5,"HIGH",IF(B13=14.5,"BORDERLINE HIGH",IF(B13=13.5,"BORDERLINE LOW",IF(B13<13.5,"LOW",)))). And if G2 is an "M" then the formula needs to look like =IF(B13>15,"HIGH",IF(B13=15,"BORDERLINE HIGH",IF(B13=14,"BORDERLINE LOW",IF(B13<14,"LOW",))))
    =IF(G2="F",IF(B13>14.5,"HIGH",IF(B13=14.5,"BORDERLINE HIGH",IF(B13=13.5,"BORDERLINE LOW",IF(B13<13.5,"LOW",)))),IF(G2="M",IF(B13>15,"HIGH",IF(B13=15,"BORDERLINE HIGH",IF(B13=14,"BORDERLINE LOW",IF(B13<14,"LOW",)))),"what to do if G2 is not a M or F"))
    Last edited by etaf; 05-28-2013 at 06:53 PM.

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    iowa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Determine a set of values to use based on another cell's data, AND returning a blank c

    Magnificent! Both of those formulas worked beautifully!

    I did add (B5="","", to the beginning of the formula where the G2 cell needed to be referenced and that works also

    Thank you SOOOOO much!

  4. #4
    Registered User
    Join Date
    05-28-2013
    Location
    iowa
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Determine a set of values to use based on another cell's data, AND returning a blank c

    I neglected to mention something that I had yet to do and that is linking 2 worksheets.
    I have Lab Results worksheet and Report worksheet.
    So I linked cell B5 from Report worksheet to cell C4 from Lab Results worksheet so that formula is ='Lab Results'!C4
    and then the formula
    =IF(B5="","",IF(B5>100,"HIGH",IF(B5=100,"BORDERLINE HIGH",IF(B5=85,"BORDERLINE LOW",IF(B5<85,"LOW",)))))
    that used to return a blank cell if there was nothing in B5 now returns "LOW" in C5.
    Can this be overcome?

    Thanks !

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,173

    Re: Determine a set of values to use based on another cell's data, AND returning a blank c

    this is probably because if cell ='Lab Results'!C4 is blank then B5 is showing zero 0
    put the following into B5

    =IF('Lab Results'!C4="","",'Lab Results'!C4)

+ 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