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

 

[Glasgowsmile]

The bracket number could probably range between -100 and 100 in most cases, I'd say. It could show up as [-10] or [+10] as well.

That information is being pulled from another worksheet. I just need to extract the number between the brackets in the original export of the data using VBA or include some line into my IF statement that ignores those brackets, if possible.

No special formatting.

 

[jtakw]

Then I assume the value is TEXT, I ask because data extraction is Not universal, we Need to know What the data is before we can come up with the correct formula.

I'll post a formula in a few minutes.

 

[Glasgowsmile]

Good to know, is there a clear way to determine the data value - whether text or number?

 

[jtakw]

Yes, in an unused cell put =ISTEXT(cell reference) and/or =ISNUMBER(cell reference), what do you get?

Also look at the formula bar when you click on subject cell, what do you see?

I also need to know, are Both cells BFR!B2 and Sheet4!B2 in that format? Do we need to extract the number between the brackets for Both? Would there be occasions where the cells Only contain numbers Without Brackets?

 

[Glasgowsmile]

Cool - it is TEXT.

Correct, BFR!B2 and Sheet4!B2 are both in the same format and will need to extract the number between the brackets on both. Most of the time there are no brackets.

 

[jtakw]

See how this works out for you, I didn't test the formula as I didn't create Sheets to test:


=IF(OR(Sheet4!B2={"NP2","CLOSED"},BFR!B2={"NP2","CLOSED"}),"",1-(IF(AND(ISNUMBER(Sheet4!B2+0),ISNUMBER(BFR!B2+0)),(Sheet4!B2+0)/(BFR!B2+0),(MID(SUBSTITUTE(Sheet4!B2," ",REPT(" ",100)),100,100)+0)/(MID(SUBSTITUTE(BFR!B2," ",REPT(" ",100)),100,100)+0))))

 

[Glasgowsmile]

Ended up being blank in the cell with the formula.

Just wanted to make sure we're on the same page with this first though -- for this example: [+10]139.99, I'm trying to pull the 139.99 not the number within the bracket. I feel that I may have confused you so I just wanted to make sure I was clear on that.

 

[jtakw]

I understand that part.
But looks like you may have different formats in those cells.
Please show a few samples of possible formats (i.e. [+10]139.99, [+10] 139.99 [-1], [1]139.99[10], 139.99[10], +10 139.99 -10,etc.)

Also, you say most times there are No brackets, when That happens, would it be for Both cells, or could be 1 with bracket and the other without?
Really need you to Post some samples.

 

[Glasgowsmile]

Here is a photo of the spreadsheet. The way it looks in this photo is also how the data displays within the Formula bar.

It is possible that in some cases BFR!B2 wont have brackets but Sheet4!B2 would have brackets and vice versa.

https://imgur.com/a/1tRYEGc