I've searched the web...but I have no idea how exactly to describe the problem I'm having simply enough. I have a template built for checking hundreds of different conditions. It's basically a template used to check temperatures, pressure, heat up rates, cool down rates, and dozens of other features for a giant oven. Some of my formulas have gotten quite complex. It took me weeks to build this template and now I have to create 125 more...I started out by trying to update every single formula in the template and then auto-filling them, but it just takes way too long to make sure they are all correct, and every single template is going to have different conditions. I decided to just create a tolerance tab and have every cell reference a bunch of cells on this tab. Some templates will have a minimum and maximum heat up while some will only have a maximum...while others will have neither. I want to keep all my ridiculous formulas the same, but I want them to just ignore certain portions of themselves if the tolerance isn't used (or if only one side of the tolerance is used).
For example, I have this formula in cell W3 (by far the shortest and simplest formula in my workbook): "IF(ISBLANK(TData!E1),"Removed",IF(AND(W4>=Tolerances!$B$23,W4<=Tolerances!$C$23),"Pass","Fail"))"
In that formula I want the cell to read "Removed" if there's no data on the TData tab. This works fine. Cell W4 is the sum of all the cells below it. I want the cell to read "Pass" if cell W4 is between the tolerances of the cells listed on the Tolerances tab. The first template I built had an upper and lower tolerance (Tolerances tab cells B23 and C23 respectively). I had no problems with that one. The one I'm currently building only has a lower tolerance (entered in B23 of the Tolerances tab). I tried leaving cell C23 blank, entering "#N/A", and "#NA(). Leaving it blank automatically gives a "Fail" even when it should be passing. Entering #N/A just make #N/A appear instead of "Pass" or "Fail". Entering #N/A() seemingly allows the formula to work correctly.
The problem: Certain other cells won't work with #N/A().
For example, this formula: "IF(ISNA(Heat_Up_3!C2),"Removed",IF(AND(Heat_Up_3!C2>=Tolerances!$B$4,Heat_Up_3!C2<=Tolerances!$C$4), "Pass", "Fail"))". For some reason, this shows as fail when it shouldn't if I enter "#N/A()" on the tolerances tab for B4. But! If I leave it blank, it seems to work fine! What am I doing wrong!? I want this to be consistent! I feel like I just sparked my brain out from weeks of staring at formulas, but I cannot figure out why entering #N/A() works with some formulas, but not others. Anyone have any suggestions? Or maybe altered formulas I could try for the examples I gave?
For example, I have this formula in cell W3 (by far the shortest and simplest formula in my workbook): "IF(ISBLANK(TData!E1),"Removed",IF(AND(W4>=Tolerances!$B$23,W4<=Tolerances!$C$23),"Pass","Fail"))"
In that formula I want the cell to read "Removed" if there's no data on the TData tab. This works fine. Cell W4 is the sum of all the cells below it. I want the cell to read "Pass" if cell W4 is between the tolerances of the cells listed on the Tolerances tab. The first template I built had an upper and lower tolerance (Tolerances tab cells B23 and C23 respectively). I had no problems with that one. The one I'm currently building only has a lower tolerance (entered in B23 of the Tolerances tab). I tried leaving cell C23 blank, entering "#N/A", and "#NA(). Leaving it blank automatically gives a "Fail" even when it should be passing. Entering #N/A just make #N/A appear instead of "Pass" or "Fail". Entering #N/A() seemingly allows the formula to work correctly.
The problem: Certain other cells won't work with #N/A().
For example, this formula: "IF(ISNA(Heat_Up_3!C2),"Removed",IF(AND(Heat_Up_3!C2>=Tolerances!$B$4,Heat_Up_3!C2<=Tolerances!$C$4), "Pass", "Fail"))". For some reason, this shows as fail when it shouldn't if I enter "#N/A()" on the tolerances tab for B4. But! If I leave it blank, it seems to work fine! What am I doing wrong!? I want this to be consistent! I feel like I just sparked my brain out from weeks of staring at formulas, but I cannot figure out why entering #N/A() works with some formulas, but not others. Anyone have any suggestions? Or maybe altered formulas I could try for the examples I gave?
