Results 1 to 6 of 6

Thread: Nested if isblank, or, and excel function.

  1. #1
    Junior Member
    Join Date
    Sep 2011
    Posts
    9
    Rep Power
    0

    Nested if isblank, or, and excel function.

    I have an =IF(ISBLANK statement below that works.

    What I need to add to it are two additional tests:

    Test 1: if N2 ISBLANK AND Q2 is equal to or greater than zero, the formula needs to return "100%"

    Test 2: if N2 and Q2 ISBLANK, the formula needs to return "Request Info"

    Current Formula:

    Code:
    =IF(ISBLANK(N2),"Request Info",IF(N2=0,"100%",1-((N2)/((P2)-(O2-Q2))))) 

    Scenario 1
    N2=ISBLANK
    Q2=0
    Return "100%"

    Scenario 2
    N2=ISBLANK
    Q2=ISBLANK
    Return "Request Info"

    Note: the formula needs to be relative since it's used in an Excel Table.


    Many thanks for your help.
    Last edited by larryt1940; 10-02-2014 at 11:00 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    =IF(AND(ISBLANK(N2),ISBLANK(Q2)),"Request Info",IF(AND(ISBLANK(N2),Q2=0),"100%",1-((N2)/((P2)-(O2-Q2)))))
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Sep 2011
    Posts
    9
    Rep Power
    0
    Hi Admin,

    Thank you very much for your help.

    I came up with the following code that is pretty close to your's. Is there any difference that you can see, other than dropping the "and" on the second condition and wrapping the code in the "iferror" statement?

    Code:
    =IFERROR(IF(AND(ISBLANK(N2),ISBLANK(Q2)),"Request Info",IF(AND(ISBLANK(N2),AND(Q2>=0)),"100.0%",1-((N2)/((P2)-(O2-Q2))))),"100.0%")




    LarryT
    Last edited by larryt1940; 10-03-2014 at 06:32 PM.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    =IF(N2&Q2="","Request Info",IF(N2&Q2="0","100%",1-((N2)/((P2)-(O2-Q2)))))

  5. #5
    Junior Member
    Join Date
    Aug 2014
    Posts
    10
    Rep Power
    0
    If your cell is already formatted as %, you can replace the "100%" with 1
    without quotes.

  6. #6
    Junior Member
    Join Date
    Jul 2021
    Posts
    1
    Rep Power
    0
    Yes, IF is a function in Excel. It has to be entered while in "formula" mode (pressing the Function Key F2 on your keyboard). For example, say you want to know how many times "F" appears on all the sheets within the workbook. You would type:
    =IF(ISBLANK("Sheet1"),0,TRIM(F))
    into cell G3 and copy and paste that formula onto all cells following incrementally higher numbers. The result would look like this:
    =IF(ISBLANK("Sheet1"),0,TRIM(FI))

    The solution should be relatively straightforward in Excel (though it will take a little more time), and can be done by combining two nested IFs:
    1) IF function with condition: if date field equals 0/01/1970 then result="never"
    2) IF function with range of cells: if date field is not equal to 0/01/1970 then result=average(B4, B5, D4). With some minor modifications or adjustments for specific needs, one instance of a true nested IF should suffice in most cases.
    Hopefully this resource will help
    https://www.exceltrick.com/formulas_macros/excel-if-statement/

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. Nested If Formula With Multiple Conditions
    By lprc in forum Excel Help
    Replies: 10
    Last Post: 04-22-2013, 07:27 PM
  3. Nested Search Function Excel Formula
    By trankim in forum Excel Help
    Replies: 6
    Last Post: 10-29-2012, 10:29 PM
  4. Excel Nested IF Function With 3 Conditions
    By patsir in forum Excel Help
    Replies: 3
    Last Post: 08-25-2012, 07:15 PM
  5. Excel Nested IF 3 Condition Formula
    By yomgi in forum Excel Help
    Replies: 2
    Last Post: 02-20-2012, 11:50 PM

Posting Permissions

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