Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correct formula?
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))} |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is correct formula? | New Users to Excel | |||
What is the correct formula for IF C5 = Y, then L5 *.06? | Excel Worksheet Functions | |||
Need the correct formula | Excel Discussion (Misc queries) | |||
Please Correct the formula | Excel Discussion (Misc queries) | |||
How do I correct this formula? | Excel Worksheet Functions |