Show Null As %0

  • Thread starter Thread starter Ardy
  • Start date Start date
A

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
 
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
 
Its just a Boolean factor. if K108 is blank, then the factor is zero and so
is the final result.
 
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<>"")
 
Thanks Guys......
It sure helps a lot.......
Ardy
JE said:
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<>"")
Click to expand...
 
Back
Top