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

--
__________________________________
HTH

Bob

"Pyrite" wrote in message
...
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))}