Nested if isblank, or, and excel function.

[larryt1940]

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:

=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.

[Admin]

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

[larryt1940]

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?

=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

[snb]

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

[Scott Huish]

If your cell is already formatted as %, you can replace the "100%" with 1
without quotes.

[zacharyswift829]

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/