IF AND Formula Issue

[Glasgowsmile]

Good Afternoon,

Minor issue with this IF AND Formula, I cannot get the results section to be more dynamic.

As I copy the code down the page it stays Sheet4!B2/BFR!B2, but I need it to move to B3, etc. How would I go about that?

The rest of the formula moves down correctly, just not the if true values.

=IF(AND(BFR!B2="NP2", OR(BFR!B2="CLOSED")),"1-(Sheet4!B2/BFR!B2)","")

 

[jtakw]

Hi,

That's because you have quotes around what you wanted as a calculation, so it's just Text, remove the quotes:

=IF(AND(BFR!B2="NP2", OR(BFR!B2="CLOSED")),1-(Sheet4!B2/BFR!B2),"")

Also, you probably only need the AND or OR function here, explain your logic, as it is, the OR doesn't do Anything.

 

[Glasgowsmile]

Ah thanks. I thought I had tried that.

I realized I had the statement wrong though anyhow, which brings up another problem. When this AND OR is false I need it to compare values between two other sheets. When the Cell in BFR shows NP2 or CLOSED, I need it to keep the cell blank but currently it's just giving me #VALUE .

Thoughts?

=IF(AND(BFR!B2="NP2", OR(BFR!B2="CLOSED")),"",1-(Sheet4!B2/BFR!B2))

 

[jtakw]

I realized I had the statement wrong though anyhow, which brings up another problem. When this AND OR is false I need it to compare values between two other sheets. When the Cell in BFR shows NP2 or CLOSED, I need it to keep the cell blank but currently it's just giving me [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] .

That's why I asked about your logic, if it's like you said in red above, then your formula should be as follows, since BFR!B2 Can't be NP2 AND CLOSED at the same time.

=IF(OR(BFR!B2={"NP2","CLOSED"}),"",1-(Sheet4!B2/BFR!B2))

After correcting the formula, do you still get #VALUE ?
If so, what's exactly in BFR!B2, Sheet4!B2

 

[Glasgowsmile]

Thanks,

That works... however, noticed another problem. The BFR Page and the Sheet4 Page can both have cells that list NP2 or CLOSED. Am I able to nest another logic line to review both BFR and Sheet4 for NP2 and CLOSED and come back blank if either or both show it?

 

[jtakw]

I'm pretty sure it can be done.
Please explain in detail exactly what cells in what sheets needs to be checked for what conditions, and if it's true, what you want, if it's false, what you want.

 

[jtakw]

May be you mean this, guessing:

=IF(OR(Sheet4!B2={"NP2","CLOSED"},BFR!B2={"NP2","CLOSED"}),"",1-(Sheet4!B2/BFR!B2))

 

[Glasgowsmile]

You've been so helpful. 1 issue did show up though.

It came to my attention that on the BFR Sheet or the Sheet4, it could display data as such

[-10.00] 139.99 [-]

in the cell. When this occurs, I get a #VALUE error because it seems the formula doesn't know what number to look at. I need to pull that 139.99 or whatever number may be listed after the brackets. Is that possible?

 

[Glasgowsmile]

Just to clarify a little more, I want to exclude the bracketed information from the formula below, just now sure how to exclude a certain character.

=IF(OR(Sheet4!B2={"NP2","CLOSED"},BFR!B2={"NP2","CLOSED"}),"",1-(Sheet4!B2/BFR!B2))

 

[jtakw]

What other possible values can be in the cells besides [-10.00] 139.99 [-] ?
How and/or where did that value come from?
Is it TEXT or Numbers with Custom Formatting?
When you click on the cell containing that value, What do you see in the Formula Bar?