Forum Discussion

Jupiter1's avatar
Jupiter1
Copper Contributor
May 07, 2020

Formula #NA ERROR

Hi All,

I have a small issue with a formula error, hoping someone can help.

 

I HAVE A FORMULA WHICH RELIES ON TWO CELLS HAVING A VALUE EQUAL OR MORE THAN 1.

I’VE USED THE FORMULA BELOW WHICH WORKS IF THERE IS A VALUE IN BOTH ……BUT IF EITHER CELL HAS A VALUE OF 0, THEN IT DISPLAYS ‘#NA’ ERROR.

I’D LIKE IT TO JUST DISPLAY A BLANK CELL.

=IFS(AND(Y2>=1,$AG$2>=1),1,,1)

 

Many thanks

excel

7 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 07, 2020
    you are using "ifs" instead of "if" so it is looking at the additional parts as an additional condition. try =if(and(y2>=1,$ag$2>=1),1,"")
    • Jupiter1's avatar
      Jupiter1
      Copper Contributor
      May 07, 2020

      mtarler 

      Hi again, one more question… is there a way to allow the cell with this formula to be adjusted manually without the formula disappearing?

      Example: cell A2 has this formula

      =if(and(y2>=1,$ag$2>=1),1,"")

      But if I want to manually delete the result to produce a blank cell (whilst the values are true in y2&ag2)…… it clears the formula also.

      What I’d like to happen, is if I delete the result in cell A2, it will reinstate once I change the value again in either y2 or AG2

       

      Hope it’s possible

      • mtarler's avatar
        mtarler
        Silver Contributor
        May 07, 2020
        yes and no. I don't understand exactly what you want or why (which if we know we might be able to better supply an alternative or workaround or just better solution), but you can most likely do what you want using a macro/VBA. Using just cell formulas, you can't have a formula get deleted and magically reappear on its own. That said, there are usually ways to achieve what you want using formulas and/or helper columns.

Resources