Correct formula?

[Pyrite]

Hi,

I have been posting here for about a month now and I focus on trying to
learn, I rarely just accept an answer offered to me without understanding it
first. I just wondered if someone could have a look over the formula I have
written (by far the most complex I have ever done from scratch) and see if it
is the 'correct' and most efficient way.

I have an Access ddatabase that exports data to Excel. There is an Amount
column (column 'N') and an exchange rate column (column 'O'). If the order is
not international then there is no information in O, if it is then there is
an exhange rate in O. In column P I want N/O if there is an exchange rate and
if there isnt one I simply want the value from N transferred to column P.

Unfortunately it isnt that simple as the data transferred from access is in
an odd format which due to me not being in control of the database it is very
possible I will just have to live with.

If an exchange rate is present then there is 14 spaces and a / before the
four character rate. If no rate is present then the cell is not empty,
instead there is 35 spaces.

Due to this I have had to write a formula to say IF column O is not blank
then divide column N by column O (value by exchange rate) but I have had to
put a MID in to tell the formula which part of O to divide by. I have then
had to add a NOT to say IF column O is bigger than empty but NOT containing
35 spaces divide N by O and if the IF results in FALSE then divide by 1,
keeping the same value figure as required.

Here is the formula I have written, it works but a more experienced eye may
look at and think NOOOOOOOO!!!! As it may be 'technically' wrong. Please feel
free to correct me or offer advice.

={N2/(IF((O2<>"")*(NOT(O2="
")),(MID(O2,16,4)),1))}

 

[Rick Rothstein]

I'm not sure why you are showing the curly braces { and } in your formula...
they would normally be put into the formula bar if you array-entered the
formula... but I don't see an array in your formula, so array-entering it
doesn't seem correct. In any event, assuming we are talking about a normal
formula, and given that the O2 value is coming from an database and can only
be one of two set formats, I think this formula does what your now working
formula does...

=N2/IF(TRIM(O2)<>"",MID(O2,16,4),1)

 

[Mike H]

Hi,

Maybe this

=IF(ISERROR((N2/RIGHT(O2,4)*1)),N2,N2/RIGHT(O2,4)*1)

Mike

 

[Bob Phillips]

Don't know about right/wrong, good/best, but this is how I would write it

=IF(TRIM(O2)="",N2,N2/SUBSTITUTE(TRIM(O2),"a/",""))

 

[Pyrite]

Thank you all for your prompt responses. This is what I love about Excel,
three different people and three different solutions to the same problem. I
will try each one and in turn try and understand them so hopefully my formula
writing will be a little more efficient in future. I have been trying to get
my head around array formula and when they are necessary, if anyone knows of
a good document to read I would much appreciate the guidance.

Thanks again for your help.

 

[Rick Rothstein]

Here is the simplest (well, if not "simplest", at least shortest) formula I
could find which I think you will have fun figuring out why it works...

=N2/MID(TRIM(O2)&"/1",2,4)

The only thing this formula requires is that there be zero or more *real*
spaces (that is, the character produced by hitting the space bar, which is
ASCII code 32) in front of the slash (which doesn't have to be a slash...
any single character that is not a space will work) and your 4 digit rate
(the rate, when present, must *always* have 4 digits in it); if there is no
rate present, then the formula requires O2 to contain zero or more *real*
spaces.