formula with error notice

Question

Hello - > I have one problem see below.

If C1251="-" and If D1251="-"

Then I must get the answer in in H1251="-"

and so on.. But there is error coming what to with teh formula

---

=IF(OR(ISBLANK(C1251="-"), ISBLANK(D1251="-")),

"-", IFS(D1251 < -C1251, "F-I",

AND(D1251 >= -C1251, D1251 <= C1251), "F-II",

AND(D1251 > C1251, D1251 <= 3*C1251),"F-III",

D1251 > 3*C1251, "F-IV"))

---

Answer 1

Hello @Chaturvedi, Santosh,

Thank you for your message. I have checked your formula and noticed the root cause of the issue:

The function ISBLANK is being used incorrectly. ISBLANK() only works when you pass a cell reference and it checks if the cell is truly empty. In your case, when you write ISBLANK(C1251="-"), Excel first evaluates C1251="-" (which returns TRUE or FALSE), and then checks if that result is blank, which will never be the case. So the “-” condition is never triggered.

Addition, Excel does not treat “-” as a number. In your data, cells contain the text “-”, not a blank and not a numeric value. Because of that, when the formula tries to calculate expressions like -C1251 or 3*C1251, Excel cannot perform math operations on text, which leads to the #VALUE! error.

To resolve this, you can directly compare the cell value with “-” instead of using ISBLANK. For example:

=IF(OR(C1251="-", D1251="-"), "-",

IFS(D1251 < -C1251, "F-I",
AND(D1251 >= -C1251, D1251 <= C1251), "F-II",
AND(D1251 > C1251, D1251 <= 3*C1251), "F-III",

D1251 > 3*C1251, "F-IV"))

This way, the formula will correctly handle rows where the value is “-” and avoid the error.

Please let me know if you have any further questions, I’ll be happy to help.

Best regards,

---

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.

Answer 2

well i did it like this--Is this corerct ??

---

=IFERROR(IF(OR(ISBLANK(C701), ISBLANK(D701)),

"-", IFS(D701 < -C701, "F-I",

AND(D701 >= -C701, D701 <= C701), "F-II",

AND(D701 > C701, D701 <= 3*C701),"F-III",

D701 > 3*C701, "F-IV")),"-")