Show text or a blank, depending on value in other cell

Dear all,
I am a novice in excel and don't seem to be able to solve this simple formula. Referring to the attachment I have a field for a number. If the number falls within a parameter it will display a message ie. "DEFCON 4". If there is no value or DIV/0 the status will show nothing - blank.
I am sure for you experts this is a no-brainer and you are probably laughing!
Thanks again for your time and effort.

God bless...

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Need help with a formula PLEASE!" does not tell us about your question. I have updated it for you this time because you are a new member. --6StringJazzer

Welcome to the Forum JobReza!


No question too small

Formula implemented in attachment

Formula:

Please Login or Register  to view this content.

Hello! In Excel, the #DIV/0! The error occurs when you try to divide a number with a zero, or you have a value equivalent to a zero, which can be a blank value. You can fix it by using an IFERROR formula.

In cell D2, try the formula below. The formula states that if the value in cell A10 (Input value 0.65) is greater than the value in column A and smaller than the value in column B to return a "DEFCON 4" result and if not, to not display anything at all. With the current data you've provided, the formula provided did not return a #DIV/0! result. This can be used to return a text result of "DEFCON 4" (or whatever text/number you wish) beside each row of data. My apologies for misinterpreting that you wanted a calculated result in a single cell.

=IF(AND($A$10>A2,$A$10<B2),"DEFCON 4","")

Hope this helps!

Hi,
Can I bother you with one last question? If the value in A10 is the result of a cell divided by zero (DIV/0), is there anyway to fix it and display another status ie. "Free Clear" in cell B10?
Pertaining to this formula: IF(A10="","",INDEX($C$2:$C$7,MATCH($A$10,$A$2:$A$7,1)))
Thanks again.

Hello JobReza!

The current formula =IF(A10="","",INDEX($C$2:$C$7,MATCH($A$10,$A$2:$A$7,1))) returns a #DIV/0! result (cell B3 has a value of zero, and cell C3 function is =A3/B3; you will receive a #DIV/0! result if you try to divide by zero). To fix this, all you need to do is embed this entire formula inside of an IFERROR formula.

IFERROR has two arguments: "value" and "value if error". The value is IF(A10="","",INDEX($C$2:$C$7,MATCH($A$10,$A$2:$A$7,1))) and the "value if error" is "Free Clear" (inside the quotation marks you can specify whatever text you want, or if you don't want anything displayed, use "").

The formula is: =IFERROR(IF(A10="","",INDEX($C$2:$C$7,MATCH($A$10,$A$2:$A$7,1))),"Free Clear")

Hope this helps!