Need blank cells to remain empty

[Bebe]

I created a basic formula to calculate the number of days between two dates
in a worksheet, i.e. =K2-A2. The formula worked fine in the 1st cell,
accurately calculating the number of days, so I dragged the formula to the
remaining cells in Column K through 100. The problem is that in the cells
where there is no value entered yet, Excel is calculating and returning
negative numbers, -39728, -39752, etc. I'd like the cells without values to
remain blank until information has been entered. Thanks in advance for the
help.

 

[Kassie]

Use =IF(OR(K2="",A2=""),"",K2-A2)

 

[Bebe]

Not sure that I understand the formula. Is this ONE formula or does the OR
mean that you're giving me a different option? Sorry to be such a dunce.
Thanks.

 

[Kassie]

You enter it as I quoted it!

Not sure that I understand the formula. Is this ONE formula or does the OR
mean that you're giving me a different option? Sorry to be such a dunce.
Thanks.

 

[joeu2004]

Sorry about the essentially duplicate posting. I know how unduly
intolerate some people in these newsgroups are of such things. They
tend to shoot (their mouths off) first, ask questions later (if at
all).

I am making tweaks to my (new) OE options to get things formatted the
way I want (or close), which gave rise to the second posting. I
thought I had aborted the first posting. (A senior moment.)

 

[JoeU2004]

Not sure that I understand the formula. Is this
ONE formula or does the OR mean that you're giving
me a different option?

To answer your questions.... Yes, it is one formula, by definition. And
yes, Kassie __might__ be giving you a "different" option than you requested.
It seems to be exactly what you asked for. But only you can know for sure.
After you understand the explanation (below), if it is not what you want,
please clarify your requirements.

You wrote originally:

I created a basic formula [....], i.e. =K2-A2.
[....] The problem is that in the cells where there
is no value entered yet, [....] I'd like the [result]
to remain blank until information has been entered.

Note that you said "cells". That begs the question: which cells? Kassie
reasonably interpreted you to mean blank cells in either column A or K. To
that end, Kassie's formula seems correct as written.

You said you do not understand the formalu. Kassie's formula says: if
either A2 or K2 is blank, return a null string (which appears blank);
otherwise, return K2-A2.

But perhaps you are only concerned about blank cells in column A or column
K, not both. If so, pick the appropriate formula below:

=if(A2="","",K2-A2)

=if(K2="","",K2-A2)

On the other hand, perhaps you are aware that the result "" is not exactly
the same as leaving the cell "empty", and your intent is for the formula to
leave the cell truly "empty". That simply cannot be done, by definition.
So if the above formula (any one) is in M2, for example, any reference to M2
will have to be made to work even if M2 contains "". There are many ways to
do that. Post back if you need help with that.

HTH.


----- original posting -----

 

[JoeU2004]

Not sure that I understand the formula. Is this
ONE formula or does the OR mean that you're giving
me a different option?

To answer your questions.... Yes, it is one formula, by definition. And yes, Kassie __might__ be giving you a "different" option than you requested. It seems to be exactly what you asked for. But only you can know for sure. After you understand the explanation (below), if it is not what you want, please clarify your requirements.

You wrote originally:

I created a basic formula [....], i.e. =K2-A2.
[....] The problem is that in the cells where there
is no value entered yet, [....] I'd like the [result]
to remain blank until information has been entered.

Note that you said "cells". That begs the question: which cells? Kassie reasonably interpreted you to mean blank cells in either column A or K. To that end, Kassie's formula seems correct as written.

You said you do not understand the formalu. Kassie's formula says: if either A2 or K2 is blank, return a null string (which appears blank); otherwise, return K2-A2.

But perhaps you are only concerned about blank cells in column A or column K, not both. If so, pick the appropriate formula below:

=if(A2="","",K2-A2)

=if(K2="","",K2-A2)

On the other hand, perhaps you are aware that the result "" is not exactly the same as leaving the cell "empty", and your intent is for the formula to leave the cell truly "empty". That simply cannot be done, by definition. So if the above formula (any one) is in M2, for example, any reference to M2 will have to be made to work even if M2 contains "". There are many ways to do that. Post back if you need help with that.

HTH.


----- original posting -----

 

[Bebe]

Thank you! The formula you provided was what I needed. Ii should have been
more clear in my original question as the blank cells only appeared in row K.
--
Bev

Not sure that I understand the formula. Is this
ONE formula or does the OR mean that you're giving
me a different option?

To answer your questions.... Yes, it is one formula, by definition. And
yes, Kassie __might__ be giving you a "different" option than you requested.
It seems to be exactly what you asked for. But only you can know for sure.
After you understand the explanation (below), if it is not what you want,
please clarify your requirements.

You wrote originally:

I created a basic formula [....], i.e. =K2-A2.
[....] The problem is that in the cells where there
is no value entered yet, [....] I'd like the [result]
to remain blank until information has been entered.

Note that you said "cells". That begs the question: which cells? Kassie
reasonably interpreted you to mean blank cells in either column A or K. To
that end, Kassie's formula seems correct as written.

You said you do not understand the formalu. Kassie's formula says: if
either A2 or K2 is blank, return a null string (which appears blank);
otherwise, return K2-A2.

But perhaps you are only concerned about blank cells in column A or column
K, not both. If so, pick the appropriate formula below:

=if(A2="","",K2-A2)

=if(K2="","",K2-A2)

On the other hand, perhaps you are aware that the result "" is not exactly
the same as leaving the cell "empty", and your intent is for the formula to
leave the cell truly "empty". That simply cannot be done, by definition.
So if the above formula (any one) is in M2, for example, any reference to M2
will have to be made to work even if M2 contains "". There are many ways to
do that. Post back if you need help with that.

HTH.


----- original posting -----