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.

"Pyrite" wrote:

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))}