IF, AND & ISBALNK

Dear @Chaturvedi, Santosh,

I hope you’re having a good day.

Thank you for reaching out and for your clear explanation.

From what I can see in your example, your current formula already works for the range condition, and now you’d like to extend it so that when the value in column D is “blank” (shown as “-”), column H should also return “-” instead of calculating.

Based on your description, the key point to clarify is how Excel treats that “-” value.

Even though it looks like a blank cell, in most cases Excel does not consider “-” as empty. It is usually treated as either: a text value ("-"), or a formatted display (for example when the actual value is 0)

Because of that, a function like ISBLANK() will not behave as expected here.

To handle your requirement correctly, you need to check the value in column D before applying your existing IF/AND logic.

A simple way to do that is:

=IF(D3766="-","-",IF(AND(D3766<$E$3210,D3766>=$E$3207),4,7))

How this works

• If D3766 contains "-" > return "-" directly
• Otherwise > apply your original formula IF(AND(...), 4, 7)

If you want to make it more robust. In case some rows might be truly empty (not just "-"), you can extend it like this:

=IF(OR(D3766="",D3766="-"),"-",IF(AND(D3766<$E$3210,D3766>=$E$3207),4,7))

Note: The behavior of your formula depends on what the cell really contains, not just what it displays. So if needed, you can double-check by clicking the cell, looking at the formula bar to see whether it is: empty; "-", or a number formatted to show as "-"

I hope this helps you implement the logic you need. Please feel free to reply below if you have any update or further concern.   

Thank you and wishing you success with your worksheet!

Warm regards, 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.