So i have a formula that is working well for HVAC maintenance estimates and calculations. Recently I discovered that when an estimator zeroes out the # of operational inspections it leaves the previously-stored calculations that it brought in from another worksheet with the vlookup. As such, it throws numbers off in the final calculation. I think this is where the AND would come into play.
My formula that works under normal situations is: =IFNA(VLOOKUP($B2,Units!$A:$L,MATCH(N$1,Units!$A$1:$L$1,0),FALSE),0)
What I need is is that if an estimator zeroes out the # Operational Inspections in column M then it should remove the stored values brought in from the VLOOKUP in columns N and O and leave everything else as is. In the below Snip Row 4 is a primary example of this. In Row 4, the estimator Zeroed out the recommendation of # Operational Inspections. How would I modify my formula and still maintain the use of the IFNA function. If Excel returns the N/A in any of the fields it ruins the math.
My formula that works under normal situations is: =IFNA(VLOOKUP($B2,Units!$A:$L,MATCH(N$1,Units!$A$1:$L$1,0),FALSE),0)
What I need is is that if an estimator zeroes out the # Operational Inspections in column M then it should remove the stored values brought in from the VLOOKUP in columns N and O and leave everything else as is. In the below Snip Row 4 is a primary example of this. In Row 4, the estimator Zeroed out the recommendation of # Operational Inspections. How would I modify my formula and still maintain the use of the IFNA function. If Excel returns the N/A in any of the fields it ruins the math.
