Formula #NA ERROR

[Jupiter1]

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

[#2]

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,"")

[#3]

@mtarler 

That is Perfect! Thank you for your help :)

[#4]

@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

[#5]

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.

[#6]

@mtarler 

Hi, thanks for looking at this. I have attached visual to explain. I hope it makes sense.

Many thanks

[#7]

yeah what you are asking for is really a macro. You could add in parallel A-D columns that are "force on/off" an then basically have the formula incorporate that corresponding cell also. in terms of numbers you could have those cells be -1 (force off),0 (nothing),1 (force on) and add it to the result of the checks and then compare as >0

[#8]

@mtarler 

Thank you so much for your help! That’s great news! Unfortunately, not too sure if I totally understand how to go about this. I’m unfamiliar with macros. Would you be able to show me? I’ve attached document if it helps.

Many thanks