Stopping IF command from entering zero or 1 for blank cells

[Nick S]

Dear All, I have a panel of data with some blank cells (absent observations).
If I replace the blanks with anything ("""", #NA etc.) and then run the IF
command (say, IF(B236>1,1,B236)), I get a one where the blank cells used to
be. I want the value to remain blank, instead of recording a false
'observation'. Can anyone help?

Nick

 

[T. Valko]

Try it like this...

=IF(COUNT(B236),IF(B236>1,1,B236),"")

 

[Jacob Skaria]

Try
=IF(ISERROR(B236*1),"",MIN(1,B236))

If this post helps click Yes

 

[Joe User]

IF(B236>1,1,B236))
[....]
I want the value to remain blank, instead of recording
a false 'observation'.

If B236 will only be empty or contain a null string (=''"") or a number,
then try:

=IF(B236="", "", B236)

If you want the result to be no greater than 1, per your original logic (I
wasn't sure if that was intended or a kludge), then try:

=IF(B236="", "", MIN(1,B236))

I have a panel of data with some blank cells (absent observations).
If I replace the blanks with anything ("""", #NA etc.) [....]

If your intent is replace empty cells with text other than the null string
(="""), then try:

=IF(ISNUMBER(B236), B236, "")


----- original message -----