Help with editing a formula

[Guest]

Hi,

I need to edit a formula and don't really know how to do it. This was made
by another person which is no longer here. I'll try my best to explain what
it does.

This forumla interacts with 9 other cells, the default setting in column C is:
XxxxxX###_xxxx_XXXxxxxx where column C = Column D # is an ID number.
For example F0705V363_GML_MagAPACS

Column D =CONCATENATE(E#,F#,G#,H#,L#,I#,L#,J#,K#) # is the row number
Column E =IF(AZ#="â—‹", IF(BA#="-","F","B"),IF(BA#="â—‹","I","X"))
Column F =IF(S#>0, CONCATENATE(0,LEFT(S#,3)),"xxxx")
Column G =IF(AF#="A","A",IF(AF#="V","V","X"))
Column H =B#
Column I =IF(ISBLANK(AG#)=TRUE,"xxxx",AG#)
Column J =IF(ISBLANK(AM#)=TRUE,"xxx",LEFT(AM#,3))
Column K =IF(ISBLANK(AP#)=TRUE,"xxxxx"Left(AP#,5))
Column L =_

Column B has ID numbers in it.

Now what I need to do is add Column AX in this formula that takes values
from both AX and AF. When AX has value of 1.1 and AF has either A or V, I
need Column E to respond with a 1F or 1B, instead of just F or B.

I beleive I have to modify Column E's formula to do this. If your confused,
please let me know and I will try to explain better. I thank everyone that
may try to help me.

 

[Roger Govier]

Hi

I think the following does what you want

=IF(AND(AX8=1.1,OR(AF8="A",AF8="V")),"1"&IF(AZ8="?",
IF(BA8="-","F","B"),IF(BA8="?","I","X")),"")
I used row 8 when testing, so substituted for your # in the formula.

 

[Guest]

Hi Roger,

Thank you for your help. I tried your formula, but I don't get a 1B or 1F.
Do you think I need to modify some other cell formula? Thank you again.

 

[Guest]

Hi Roger,

Nevermind, it worked, chalk it up to user error, I thought I copied your
formula exactly. Thank you again for your help

 

[Guest]

Hi again,

One problem now with that formula, if I enter 1.0 in cell AX, and either â—‹
or - in cell BA, I loose my F or B. Is there something else I need to do to
the formula? Thank you.

 

[Roger Govier]

Hi

You originally asked for the test to be AX=1.1
That's what the formula looks for. Are you saying you need a range of
values for AX?

Maybe
=IF(AND(AX8>=1,AX8<=1.2,OR(AF8="A",AF8="V")),"1"&IF(AZ8="?",
IF(BA8="-","F","B"),IF(BA8="?","I","X")),"")

 

[Roger Govier]

Hi

I have only just noticed that my NG reader has changed the degree
symbol in the formula to a "?"
In both cases, where the ? appears, there should be a degree symbol.

 

[Guest]

Thank you Roger,

When I first posted,I forgot that I would still get some data that uses AX
for 1.0. JLatham also suggested a formula to use as well. I will try out
your formula as well. Thank you for your time.