Show Null As %0

[Ardy]

Hello All:
I have two cells A1 and A2 . Am calculating Percentile in cell A2.
The input is Cell A1. Please don't get hung-up on the formula in
Cell A2. it works for what I am trying to do the problem is that 0 is
also calculated as %100 which is correct( we are capturing the number
of errors - so 0 error is %100 which is no error).

The Problem is that if cell A1 is null this also reads as %100 in A2,
which creates a problem. Is there any way to have the formula read
null as null, so the percentile reflect %0.

A1 = User input
A2 =(100-(D188*0.45))/100

Ardy

 

[Ragdyer]

Try this:

=((100-(A1*0.45))/100)*(A1<>"")

 

[JE McGimpsey]

One way:

=IF(A1="",0,1-D188*0.0045)

 

[Ardy]

Thnaks Both Method Works OK,
I used =((100-(K108*0.45))/100)*(K108<>""), Could you explain
(K108<>"") is this another way of saying IF null

Ardy

 

[Guest]

Its just a Boolean factor. if K108 is blank, then the factor is zero and so
is the final result.

 

[JE McGimpsey]

The (K108<>"") term evaluates to a boolean TRUE/FALSE. When TRUE/FALSE
is used in a math operation, here *, XL coerces TRUE/FALSE to 1/0. So if
the term evaluates to FALSE, the first term will be multiplied by zero,
otherwise it is multiplied by 1.

You could do the same thing with the formula I gave:

=(1-K108*0.0045)*(K108<>"")

 

[Ardy]

Thanks Guys......
It sure helps a lot.......
Ardy

The (K108<>"") term evaluates to a boolean TRUE/FALSE. When TRUE/FALSE
is used in a math operation, here *, XL coerces TRUE/FALSE to 1/0. So if
the term evaluates to FALSE, the first term will be multiplied by zero,
otherwise it is multiplied by 1.

You could do the same thing with the formula I gave:

=(1-K108*0.0045)*(K108<>"")