Forum Discussion
Help with an If/And formula
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.......
- 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!
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, HansVogelaar 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).
