CODEWITHSUNDEEP
excelexcel-formula
RalfB
RalfB

Reputation: 583

Excel 2016: Conditional Text + sense for missing values

I have a number in cell A1 that can is between 0 and 1000, and I want to assign it a text that indicates success of failture (here: Yes and No).

=IF(A1>200,"YES","NO")

This works well for all numbers. So far, so good. Now, however, I want to create a complete spreadsheet with 100s of cells where this formula is active. These have 100s of numbered cells that are not yet set (as measurement is collected over time).

I do not want a 'No' in there. I rather have no text. How do I do that? In a way I want to only have this formula working, when there is no NA, that means, not set value. A zero is not enough as zero (0) is a number.

I think something like this:

=IF(A1=NA,"", IF(A1>200,"YES","NO"))

Is there something that compares?

Upvotes: 0

Views: 227

Answers (2)

A.S.H
A.S.H

Reputation: 29332

  • You can check A1 for #NA using IF(ISNA(A1), "",...)

  • You can check it for any error using IF(ISERROR(A1), "",...)

  • You can check it for blank using IF(ISBLANK(A1), "",...)

  • However, you only want it to be a number, so your best choice here is ISNUMBER:

.

           =IF(ISNUMBER(A1), IF(A1>200,"YES","NO"), "")

Upvotes: 1

MikeD
MikeD

Reputation: 8941

very close already ... I'd do

=IF(A1="","", IF(A1>200,"YES","NO"))

if column A originally is blank

and to take it even further ...

=IF(A1="";""; IF(TYPE(A1)<>1;"NaN";IF(A1>200;"YES";"NO")))

provides an additional check that in A1 there is a numeric value.

Upvotes: 0

Related Questions