Help with an If/And formula

[bluejay215]

Hi, I've been trying to get a formula to work for a while to no avail and I'm hoping someone here can help. I'd be very grateful for any tips as I feel like I have complete tunnel vision at this point.

Essentially, I have two variables, and I'm trying to account for each combination of options with one formula. Variable 1 is whether cell A1 says "Yes" or "No". Variable 2 is whether cell C1 is blank or not; as far as I can tell, the simplest way to construct this is running COUNT(C1=1) as an If formula.

If A1 says "Yes" and C1 is not blank, I want E1 to equal the days between C1 and B1.

If A1 says "No" and C1 is not blank, I want E1 to equal the days between D1 and B1.

If A1 says either "Yes" or "No" but C1 is blank, I want E1 to remain blank.

If A1 is blank, I also want E1 to remain blank.

The best I've been able to come up with for the formula in cell E1 is below:

=IF(AND(A1="Yes",COUNT(C1=1)),DAYS(C1,B1),
IF(AND(A1="No",COUNT(C1=1)),DAYS(D1,B1),
IF(AND(A1="Yes",COUNT(C1="")),"",
IF(AND(A1="No",COUNT(C1="")),"",
IF(A1="","")))))

This seems to be working except for when C1 is blank. When C1 is blank, rather than cell E1 remaining blank like I want, the formula is trying to count the days using the blank cell and so is coming up with -44682 which will wreck my linked statistics as it's not a date. This still happens if I sub out COUNT(C1="") for COUNT(C1=0) or COUNT(C1=<1). I'm sure I'm missing something obvious but any help would be very much appreciated. Thanks :)

[#2 · Accepted solution]

@bluejay215 

How about

=IF(OR(A1="",C1=""),"",IF(A1="Yes",C1,D1)-B1)

[#3]

That seems to be working, thanks so much!

[#4]

@bluejay215 

Variable 2 is whether cell C1 is blank or not; as far as I can tell, the simplest way to construct this is running COUNT(C1=1) as an If formula.

There is a function that is more direct: ISBLANK(C1) returns TRUE if C1 is blank, FALSE if it's not. It's also easier to read your formula that way, given that the word of the function is actually what you're seeking.

That said, I had to resort to a new function (LET) to re-write your formula in a way that I could follow.

Here's the result.' (The spreadsheet is attached)

=LET(yes,A1="Yes",no,A1="No",abl,ISBLANK(A1),cbl,ISBLANK(C1),cnbl,NOT(ISBLANK(C1)),
IF(AND(yes,cnbl),DAYS(C1,B1),
IF(AND(no,cnbl),DAYS(D1,B1),
IF(cbl,"",
IF(abl,"")))))

That might be hard to follow if you're not familiar with LET, but the idea behind it is to make the final formula easier to read. The first line above simply sets those variables into named variables. As follows

yes is true when A1="Yes"

no is true when A1="No"

abl is true when A1 is blank

cbl is true when C1 is blank

cnbl is true when C1 is not blank

From there the formula just uses those values in the same way your original formula did. 

Here's a reference that explains LET more fully. You will need the most current version of Excel for this to work. If you don't have that, then you can revert to your formulation. 

I should say that nested IF functions are among the most difficult to read....that's part of the difficulty you've encountered. I would not advise making it any longer. Instead, you might want to look into breaking it apart into several columns, using simpler formulas in each.......

[#5]

@Hans Vogelaar 

Much more to the point that my convoluted solution. Well done, Hans.

[#6]

This is very helpful too, it's interesting to see different ways of doing it and I wasn't familiar with the Let function but it will definitely come in handy when writing longer formulas - thank you!

[#7]

@bluejay215 

it's interesting to see different ways of doing it

One of the more interesting/fascinating aspects of Excel is that there are OFTEN multiple ways to get from A to B. In this instance, @Hans Vogelaar definitely gave you the more elegant, efficient way; I was caught up in the "fun" of using LET, and working down in the weeds, I missed the simpler solution.

The LET function is new, as I said, and can be used in a lot of ways. If you are able to use it, and are interested in learning more, you might also be interested in LAMBDA, which enables you to write what are in essence user-defined functions (for use throughout the workbook in which you define them).