EXCEL Formulat question 1

[realtallgal]

I have a column of numbers, from 1-5, and the rest are blank. I want to make all of the blanks N/A, the formula I have been struggling with is not working (have a feeling I am over thinking this).. I want to leave the 1-3 as they are but change the blanks to NA
=IF(OR(Q3="1",Q3 ="2",Q3 ="3"),"),"OK","NA")

 

[SkipVought]

hi,

1 is NOT EQUAL to "1"!!!! They are two completely different values!
[tt]
=IF(OR(Q3=1,Q3 =2,Q3 =3),"OK","NA")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

And for 4 and 5?

 

[SkipVought]

I have a column of numbers, from 1-5, and the rest are blank. I want to make all of the blanks N/A

Actually IN THE COLUMN WITH THE NUMBERS, you cannot do that with a formula.

You can, however, do that in an empty column. Then you could COPY/Paste Special > Paste VALUES over that column of numbers
[tt]
=IF(ISBLANK(Q3),"N/A",Q3)
[/tt]
HOWEVER, it is not a best and accepted practice to mix NUMBERS and TEXT in the same column. There can be bad consequences down the road, depending on what you need to do with this data.

BTW, "N/A" is not the same as #N/A! that Excel returns for Value Not Available.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Deniall]

And Excel's "Value not available" result can be achieved with the =NA() function.

 

[byundt]

If you want a value of 1, 2 or 3 to remain as-is
replace 4 or 5 with "OK"
display "NA" if the cell is blank

=IF(Q3="","NA",IF(OR(Q3={1,2,3}),Q3,"OK"))

If none of us have guessed what you want the formula to do, please post a table showing each of the possible inputs and what you want the returned value to be.

Brad

 

[realtallgal]

Thank you all... Brad your formula works perfectly... Many Thanks to all!!

 

[SkipVought]

@realtallgal,

Be sure to thank Brad by...
[blue]
"Like this post?
Star it!"[/blue]

Also, in the future, clearly ask the question that you want answered.

I have a column of numbers, from 1-5, and the rest are blank. I want to make all of the blanks N/A

I want to leave the 1-3 as they are but change the blanks to NA
=IF(OR(Q3="1",Q3 ="2",Q3 ="3"),"),"OK","NA")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]