Insert a BLANK value

[Guest]

I struggle to create a formula that render the cell EMPTY (NOT zero or space).
Here's what I'm trying to do:
A1: 5
A2: IF(A1>2;BLANK;"NO")
A3: =ISBLANK(A2)

If A1 contains 5 (as above) then the condition in A3 should be TRUE
If A1 contains 1, then the condition should in A3 should be FALSE.

More info: What I'm really trying to do is to automatically insert a default
value in cell A2 if A1 is not empty. This valua can subsequently of course be
overwritten manually. Finally, the sheet should calculate the time-difference
between A1 and A2. Fot this calculation to be correct when NO value is
entered in A1, A2 needs to remain empty in case A1 remains empty because the
simple subtraction of the two cells results in an error if A1 is empty and A2
is not. So I need to find a way to enter a formula in A2 that automatically
places the value that is evaluated as 'EMPTY' or 'BLANK' in A2 without this
same formula screwing up the time-calculation if there is nothing to subtract
in A1 and A2.
Hope this explanation is clear enough....
Thanks very much in advance for your help!
Jop

 

[Guest]

Jop,

I think your approach to what you are trying to do is flawed. Cell A2
evaluates A1 with the (corrected) formula =IF(A1>2,"","NO")

This formula leaves A2 empty until A1 is greater than 2 at which point it
displays NO.

You can easily put a default value as you call it in A2 if the condition is
false by using
=IF(A1>2,"Default Value","NO") but where you need to re-think is where you
say this could be overtyped because doing so would delete your formula.

To work out the time difference between A1 and A2 is simply A1-A2

With regard to A2 being empty, it never will providing it contains a formula.

 

[Guest]

maybe i am confused yet to concentrate on your simultaneous concern:

trying to avoid an error result from subtraction of A1 and A2, at the same
time having the True or False result on A3...we can use a Zero instead of
Blank since blank-0=0
IF
A1: = 5
A2: =IF(OR(A1>2,A1=0),0,"NO") : RESULT : 0
A3: =A2=0 : RESULT : TRUE

IF
A1: = 1
A2: =IF(OR(A1>2,A1=0),0,"NO") : RESULT : NO
A3: =A2=0 : RESULT : FALSE

IF
A1: BLANK
A2: =IF(OR(A1>2,A1=0),0,"NO") : RESULT : 0
A3: =A2=0 : RESULT : TRUE

 

[Guest]

Dear Mike and Driller. Thanks for your replies and obviously I failed in
making clear what I'm struggling with. So let me explain the 'business
problem'.
I'm planning my staff and want to calculate the total number of planned
hours in a week for each person. In order to do so, we capture the planned
START and END-time for each person on each day of the week. To calculate the
number of hours of planned activity for a person we subtract (on any day) the
START-time from the END-time. And we have simple planning rule: If someone is
scheduled to START after 12:00, he will END at 21:30.

Now for the model in Excel (Note that ONLY in the text below, for
readibility, I have not bothered to enter the correct numeric values for
times such as 12:00 and 21:30, I've used 'logical' references)
:
So each employee has one row on the spreadsheet. Every day has two columns:
START and END. All cells are in DATE format. To calculate the total planned
time for a week, we simply ADD all the END-times and SUBTRACT all the
START-times. So far so good.
Now, me being lazy, I want to build the rule of (IF START > 12:00, END =
21:30) into the sheet. This means that the END-cell D2 contains
=IF(C2>12:00;21:30).

This is fine if the person is scheduled to work that day: his START-cell C2
contains his scheduled start-time and EITHER the outcome of the formula in D2
is TRUE in which case D2 qill hold the value 21:30 OR the person was
scheduled to start before 12:00 and I will have manually entered the correct
planned END-time inD2, overwriting the formula. In both cases, the
calculation of D2-C2 will give the correct hours of planned work.

However, if the person is NOT scheduled to work on that day, the subtraction
of D2 minus C2 delivers an error, since D2 contains a FALSE value, or it
contains a value that is put there as a result of the False-exit of the
evaluation (E.g. =IF(C2>12:00;21:30;"BLANK") will put the text-value 'BLANK'
in the cell). Whatever I put in the FALSE-exit of the evaluation, the
SUBTRACTION of C2 from D2 will never result in a ZERO value.

In short: How do I make a cell contain NO VALUE (i.e. not a value, not zero,
not a space) as a result of an evaluation (C2 is empty).

The formula in D2 could be =IF(ISBLANK(C2);???;IF(C2>12:00;21:30;"Please
enter scheduled END-time")), which would trigger the planner to enter a value
in D2 to give the End-value if the person has a START before 12:00. But what
should the formula contain in stead of the '????'. Anything that has a value,
will obstruct the subtraction-result of D2-C2. In fact, what I would want in
the TRUE-exit of the ISBLANK, is for the formula to self-destruct and leave
the cell EMPTY (as if one had hit the DEL-key......

Same players shoot again?

 

[Pete_UK]

How about something like this in D2:

=IF(C2="","",IF(C2>12:00,21:30,"BLANK")

so D2 will only show 21:30 if there is an entry in C2 which is after
12:00. The formula can be copied to F2, H2, J2 and K2 for the other
days.

You will need to modify your subtraction formula to check that both C2
and D2 (and the other pairs) are numbers, something like:

=IF(AND(ISNUMBER(C2),ISNUMBER(D2)),D2-C2,0)+IF(AND(ISNUMBER(E2),ISNUMBER(F2)),F2-E2,0)+IF(AND(ISNUMBER(G2),ISNUMBER(H2)),H2-G2,0)+IF(AND(ISNUMBER(I2),ISNUMBER(J2)),J2-I2,0)+IF(AND(ISNUMBER(K2),ISNUMBER(L2)),L2-K2,0)

for 5 days, if I understand your description correctly. This will
subtract the paired cells ONLY if both cells contain numeric values,
otherwise returning a zero for that pair.

Hope this helps.

Pete

 

[Guest]

Pete,

Thanks for your suggestion and it is something I hadn't thought of. I'm sure
it will work as it effectively is a work-around. I'm still curious whether
there is a way to enter a formula in a cell that will leave the cell as empty
as a the Delete-button, though, as I have necountered this issue before and
am sure I will encounter it again. I feel there MUST be a way to render a
cell empty upon evalution....
Anyone?

 

[Pete_UK]

There isn't ... the cell contains the formula !!

Pete