Hide value if reference cell is blank

[carole]

I have a cell where a date gets entered. In the next cell I want to show the date for 45 days later. I enter the formula =SUM([reference cell number]+45). It works fine except if a date has not been entered in the reference cell, the result cell shows a date of 45 days after 1/1/04). I want that cell to be blank unless a date is entered in the reference cell.Can I do this? How? I tried setting a validation for the cell and checking "ignore blank". That didn't work. (I'm using Excel X for Mac)
Thanks

 

[JE McGimpsey]

I have a cell where a date gets entered. In the next cell I want to show the
date for 45 days later. I enter the formula =SUM([reference cell number]+45).
It works fine except if a date has not been entered in the reference cell,
the result cell shows a date of 45 days after 1/1/04). I want that cell to be
blank unless a date is entered in the reference cell.Can I do this? How? I
tried setting a validation for the cell and checking "ignore blank". That
didn't work. (I'm using Excel X for Mac)<br>
Thanks

One way:

A1: <date or blank>
B1: =IF(TRIM(A1)<>"", A1+45, "")


Note that SUM() isn't necessary when you use "+". It just takes the sum
of the result of the addition (which of course, is just the result of
the addition).

 

[carole]

Thank you soooo much!! I spent over 4 hours using the pop-up help in the program and searching the Knowlegebase online, and couldn't find the solution. That formula worked perfectly!

 

[Dan_Draney]

This also works:
B1:=IF(ISBLANK(A1),"",A1+45)

 

[carole]

Thanks. That works too, but the formula seems a little more straightforward.

 

[JE McGimpsey]

This also works:<br>
B1:=IF(ISBLANK(A1),"",A1+45)

Be careful if others are going to use this sheet - you'd be surprised
how many people try to "clear" a cell by pressing the spacebar. After
all, it *looks* blank...

When that happens, ISBLANK() returns FALSE, and A1+45 will return a
#VALUE! Error.

That's why I suggested TRIM(A1)="".

 

[Bob Greenblatt]

Be careful if others are going to use this sheet - you'd be surprised
how many people try to "clear" a cell by pressing the spacebar. After
all, it *looks* blank...

When that happens, ISBLANK() returns FALSE, and A1+45 will return a
#VALUE! Error.

That's why I suggested TRIM(A1)="".

Isblank may also fail trying to tell the difference between a cell that
NEVER contained anything -in which case it returns TRUE; vs. a cell that one
contained something but is now "erased". That's why I use len(a1)=0 instead.

 

[carole]

Bob - I didn't understand what you meant.
Carole

 

[Bob Greenblatt]

Bob - I didn't understand what you meant.
Carole

What I meant was, that it may be more reliable to use LEN(a1)=0 to see if A1
is blank.

 

[carole]

Bob - OK, so I entered the formula =IF(LEN(A1)=0, A1+45, ""). That also seems to work. Why would that be more reliable than IF(TRIM(A1)&lt;&gt;"", A1+45, "")?
Thanks,
Carole

 

[carole]

Bob - Wait a minute, that didn't work. It shows a value in B1, even if A1 is blank.
Carole

 

[Bob Greenblatt]

Bob - OK, so I entered the formula =IF(LEN(A1)=0, A1+45, ""). That also seems
to work. Why would that be more reliable than IF(TRIM(A1)<>"", A1+45, "")?
Thanks,
Carole

You have your if statement backwards. It should be if(len(a1)=0,²²,a1+45).
And, it isn¹t. Those 2 are equivalent. I meant that len(a1)=0 is more
reliable than isblank(a1).

 

[carole]

Bob (&amp; J.E.) - OK. That worked (with a = in front). Which do you think would be better, the LEN or the TRIM? And why?

Thanks again,
Carole

 

[JE McGimpsey]

Bob (&amp; J.E.) - OK. That worked (with a = in front). Which do you think
would be better, the LEN or the TRIM? And why?<br>

Either will work fine if the A1 is actually blank.

I just have run into too many users who use the spacebar to "clear" a
cell (since it *looks* blank) and so I tend to use

=IF(TRIM(A1)="","",A1)

IIRC, though, it is *slightly* more efficient in terms of processor
speed to use

=IF(LEN(TRIM(A1))=0,"",A1)

but if you blinked, you'd waste the 50 nanosecond difference.

 

[carole]

J.E. - Originally you said the formula should be: =IF(TRIM(A1)&lt;&gt;"",A1+45,""), not the one you just posted (The difference is in the commas, brackets and quote marks.)

The first one is the one I'm using, and it seems to work fine. None of the TRIM or LEN formulas we've discussed seem to have a problem with inserting a space in A1. (I've set the format for A1 to "Date". Maybe that's why it doesn't make any difference if someone hits the spacebar there.)

To prevent users from accidentally deleting the formulas, I've unlocked the cells they are to type into, and protected the worksheet.