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))} |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correct formula?
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) -- Rick (MVP - Excel) "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))} |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correct formula?
Hi,
Maybe this =IF(ISERROR((N2/RIGHT(O2,4)*1)),N2,N2/RIGHT(O2,4)*1) Mike "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))} |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correct formula?
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))} |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correct formula?
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))} |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Correct formula?
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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) -- Rick (MVP - Excel) "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))} |
Reply |
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 |