Refine formula with an additional IF function that conflicts with an existing IF function.

[Lea]

Hello Smart Excel People!

I sure hope there is a kind soul out there willing to take pity on me. I have been trying to fine tune the formula below off and on for a few weeks now. I am just plain stuck on this one. I have not been able to figure it out and I sure could use some help, please.

In the formula below, everything works how I want it to except the part in red. Currently, if there is an error or if there is text, it displays the message TEXT – CHECK VISUALLY. What I would like the formula to do is if the value of cell J19 is PASS then " "- the cell is blank. If the value of cell J19 is an error or any other text, then display TEXT – CHECK VISUALLY. Another way to say it is if the cell value is pass, I want it to return a blank cell. But if there is any other text value, I would like the cell to say TEXT – CHECK VISUALLY.
The rest of the formula performs as it should.

=IF(B19=" ","",IF(ISERR(OR(NUMBERVALUE(J19),NUMBERVALUE(L19),NUMBERVALUE(N19))),"TEXT - CHECK VISUALLY", IF(OR(J19=" ",J19=""),"MISSING",IF(NUMBERVALUE(J19)<NUMBERVALUE(F19),"OOS",IF(J19=" "," ",IF(NUMBERVALUE(J19)>NUMBERVALUE(G19),"OOS",""))))))

Thank you for any help you can give,
Lea

 

[awoohaw]

Hi Lea,
Can you use the xl2bb addin to post a mini worksheet? The text of the image is so small I can't recreate it.

 

[awoohaw]

what is supposed to be in L19 and N19 in your formula in M19? They are blank in the image. Well M19 appears blank. and L19 seems to be hidden.

 

[Lea]

Hi Lea,
Can you use the xl2bb addin to post a mini worksheet? The text of the image is so small I can't recreate it.

Hi Lea,
Can you use the xl2bb addin to post a mini worksheet? The text of the image is so small I can't recreate it.

Hi awoohaw,

I tried to use the addin, but my work computer will not let me unzip it. I am thinking it has to do with administrator rights(?). My screenshot is so small because I included the formula. Maybe the screen shot below will be better. L19 is blank. Column M is where the formulas that I need fixed is located.

My formula in column M is supposed to alert us if the value of J is not in the specification range of F & G.

I will keep trying to upload a sheet.

Thank you for looking,
Lea

 

[awoohaw]

work computer restrictions are frustrating, but understandable.
Thanks for the new image.
I'll see what I can do with this. It is much clearer.

 

[Lea]

work computer restrictions are frustrating, but understandable.
Thanks for the new image.
I'll see what I can do with this. It is much clearer.

Thank you so much!

 

[Fluff]

How about

=IF(B19=" ","",IF(J19="Pass","",IF(ISERR(OR(NUMBERVALUE(J19),NUMBERVALUE(L19),NUMBERVALUE(N19))),"TEXT - CHECK VISUALLY", IF(OR(J19=" ",J19=""),"MISSING",IF(NUMBERVALUE(J19)<NUMBERVALUE(F19),"OOS",IF(J19=" "," ",IF(NUMBERVALUE(J19)>NUMBERVALUE(G19),"OOS","")))))))

 

[Lea]

How about

=IF(B19=" ","",IF(J19="Pass","",IF(ISERR(OR(NUMBERVALUE(J19),NUMBERVALUE(L19),NUMBERVALUE(N19))),"TEXT - CHECK VISUALLY", IF(OR(J19=" ",J19=""),"MISSING",IF(NUMBERVALUE(J19)<NUMBERVALUE(F19),"OOS",IF(J19=" "," ",IF(NUMBERVALUE(J19)>NUMBERVALUE(G19),"OOS","")))))))

Hello Fluff,

Thank you so much for helping. I inserted formula into column M, and it does not quite work right. The red text in column O is what should appear. Really, I am OK with it saying OOS instead of text - Check visually, but row 20 is out of spec (OOS). 66 does not fall into the 55 - 65 range and the new formula left the cell blank.

Humble regards,
Lea

 

[Fluff]

That's got nothing to do with J19 = "pass" & judging by your previous images never worked.
If J should be between F & G then you have the > & < the wrong way round.

 

[Lea]

That's got nothing to do with J19 = "pass" & judging by your previous images never worked.
If J should be between F & G then you have the > & < the wrong way round.

Hello Fluff,

I really do not understand how it works, but it does properly check number values. If you look at rows 23 through 28, there are two rows that are in spec and column M shows a blank for those rows. It displays OOS for the rows that do not fall in the range.
Sorry, I may not have explained it very well.
I need the result of pass in column J to be blank in column M, and any other text in column J to say TEXT – CHECK VISUALLY in column M. Right now, any text gives me the check visually message. I would like to exempt the text "pass".

Kind regards,
Lea