Multiple embedded If statements in an If Function

Occasional Visitor

I'm trying to get Excel to evaluate 3 different cells and return the one that contains a value using ISBLANK.  I intend the formula to first look in cell J3 for a value, if there is one, return that value and stop processing the statement; if there is not a value J3, look in G3, if there is one, return that value and stop processing the statement; if there is not a value in G3 look in D3 and return that value and stop processing the statement.  There will be a value in either J3, G3 or D3 without exception.  Here is the statement I entered:  =IF(ISBLANK(J15),G15,IF(ISBLANK(G15),D15,J15))

Here is an example of the spreadsheet (The first row is the column designation; the second row is the header) (the formula is in column P and is supposed to return the out time for the day):

ABCDEFGHIJKLMNOPQ
DayDateInOutSub ttlInOutSub ttlInOutSub ttlInOutDly TtlStartStopTotal Hours
Mon01-108:0016:0008:00  00:00  00:00  08:0008:0000:00-8.00
Tue01-118:0010:0002:0010:0016:3006:30  00:00  08:3008:0016:308.50
Wed01-128:0014:3006:3014:3016:0001:3016:0017:3001:30  09:3008:0017:309.50

 

The formula works on the Wed and Tue rows, but does not work on the Mon row.  Can someone help me understand why?

 

Thanks, 

Suzanne

2 Replies

@Suzanne9808 

=IF(NOT(ISBLANK(J3)),J3,IF(NOT(ISBLANK(G3)),G3,D3))

Is this what you are looking for? 

@Suzanne9808 

 

Actually, contrary to your description, the formula IS working just fine. It's doing what it's written to do; that may not have been your intention, of course, but Excel is working just fine.

Here's how it's happening. the formula again reads like this:

=IF(ISBLANK(J15),G15,IF(ISBLANK(G15),D15,J15))

 

Your formula says, IF J is blank, the display whatever is in G

And in that top row column G is blank.

So the formula does exactly what it's been asked to do and displays that blank from G.

And stops.

 

Now, let's write a formula that does what you want it to do. Here's that description:

I intend the formula to first look in cell J3 for a value, if there is one, return that value and stop processing the statement; if there is not a value J3, look in G3, if there is one, return that value and stop processing the statement; if there is not a value in G3 look in D3 and return that value and stop processing the statement.  There will be a value in either J3, G3 or D3 without exception. 

 

You'd really be better served with an IFS function, which does stop when it meets a condition that is satisfied.

=IFS(J3<>"",J3,AND(J3="",G3<>""),G3,AND(J3="",G3=""),D3)

By the way, you notice I didn't bother with the ISBLANK--that works, there's nothing wrong with it, but I just find it easier to designate the same by a pair of quotation marks with nothing between them. It serves the same purpose in less space.

I also think I turned your logic around: if you gave voice to your first formula, it was saying to Excel, in effect:

"Look in cell J3 and if it is blank, then get the value from G3."

But this new formula reads more as your descriptive paragraph read. 

"Look in cell J3, and if it's got a value (i.e., if it's not blank), then use it."

And goes on to say, "If J3 has no value, and G3 has value, use G3"

And finally, "If J3 AND G3 have no value, use D3"