0

As best I can tell, IF([cell],TRUE,FALSE) is always returning TRUE if [cell] contains a value, but I can't find any documentation on this sort of truth-value test in Excel 2010. I don't know what it's called, and all the documentation I can find seems to require some conditional operator in the first field of the IF function. Is this shorthand, a bug, or am I using some other feature in a way that is unintended?

Here's my mess:

=IF([@[LL Billed]],NETWORKDAYS([@Closed],[@Paid]),NETWORKDAYS([@Closed],TODAY()))

All referenced cells either contain a date or are blank. Currently, if @[LL Billed] is blank and @Paid is blank (@closed always contains a date), I get an integer returned (which is how it should work).

What I need to do is change this formula so the logic test checks whether there is a value in both @[LL Billed] AND in @Paid; if both contain value it should return TRUE, and if either one contains no value then it should return FALSE.

I'd like to avoid completely restructuring this IF function because it has symmetry with other functions in the workbook. Is there another "shorthand" test I can employ as part of the logic test? I've tried nesting NOT-ISBLANK-AND functions, but to no avail, plus it's messy.

Any suggestions?

Chris
  • 19
  • 8
  • the sytax is, IF(AND(Criteria1, Criteria2), TRUE, FALSE). In other words if BOTH Criteria1 AND Criteria return TRUE, then the TRUE statement is returned if EITHER are NOT true, then False is returned. – user2140261 May 20 '13 at 15:09

3 Answers3

1

Consider the formula =IF(A1,True,False)

This will be False if A1 contains the number zero
It will be True if A1 contains a number not equal to zero
It will be False if A1 is a blank cell
All other cases it will be #VALUE

If you want to suppress #VALUE then write =IFERROR(IF(A1,True,False),"Hello") to print "Hello" instead of #VALUE.

Hope that helps a bit.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • Then I have a very odd situation going on here. Currently a blank cell is returning False (unless there's a hidden zero in there). However, the rest of your analysis certainly helps me understand the underlying logic here. – Chris May 20 '13 at 15:16
  • Yes: Barry splotted it! It's a fallback from very old spreadsheet programs I'm afraid. (A bit like your getting 0 if you write =A1 when A1 is blank). You can test additionally for IsBlank() though as a workaround. – Bathsheba May 20 '13 at 15:20
1

See if this works. 

=IF(AND([@[LL Billed]],[@Paid]),NETWORKDAYS([@Closed],[@Paid]),NETWORKDAYS([@Closed],TODAY()))

A lot of issues come into play on what you consider a value. Not blank, Greater then 0, a number vs text. Errors. a lot of variables come into play and without more information on your data this is the best I feel I can provide you with

user2140261
  • 7,855
  • 7
  • 32
  • 45
  • Oddly enough, this didn't work. Any thoughts as to why it wouldn't? I'm getting one of those helpful #VALUE! returns. – Chris May 20 '13 at 15:18
1

Try using COUNT (which counts numbers, including dates), i.e.

=IF(COUNT([@[LL Billed]],[@Paid])=2,NETWORKDAYS([@Closed],[@Paid]),NETWORKDAYS([@Closed],TODAY()))

If COUNT = 2 then both cells have dates

btw if you use an IF function with a number as the test, it always returns TRUE.....unless the number is zero, in which case it returns FALSE

Note: if you want you can shorten the formula by embedding the IF function within NETWORKDAYS, i.e.

=NETWORKDAYS([@Closed],IF(COUNT([@[LL Billed]],[@Paid])=2,[@Paid],TODAY()))

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • This worked perfectly. It's not as eloquent, but it got the job done. I'll mark in a minute. – Chris May 20 '13 at 15:18