Results 1 to 10 of 10

Thread: Formula required (if possible)

  1. #1

    Formula required (if possible)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am looking for a formula based on the following.
    Cell A1 has any number from 0 through to 360.
    Cell A2 has any number from 0 through to 360.
    Cells C1 and D1 are to return results based on the following proceedure.
    360 subtract (A1+A2) and subtract 360 if [360 subtract (A1+A2)] is greater than or equal to 360 and this to return the numerical result in cell C1 with the letter w in cell D1, and if Cell C1 is greater than or equal to 180, then 360 subtract the tesult in Cell C1 and Cell D1 to return the letter e.
    Look forward to getting help with this.
    Thank You.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    try in C1:
    =IF(MOD(360-(A1+A2),360)>180,360-MOD(360-(A1+A2),360),MOD(360-(A1+A2),360))
    and in D1:
    =IF(MOD(360-(A1+A2),360)>180,"e","w")

  3. #3
    P45cal, thank you for replying, however, the formula doesn't work as I expected and I know what the error might be if I can explain to you....it's not far off from working.....so....what I did so far is to turn around the e and w....
    The part that doesnt work I will explain as best I can.....
    In A1, I had already did the arithmetic, so this part is missing....this is my mistake in explaining what I want to have happen....
    I'll give you two examples how it should work.....If I enter 10 in A1 and 15 in A2, then C1 should return 5, not 25...
    Second example...If I enter 280 in A1 and 5 in A2, it should return 5, not 75.
    I trust this can be solved.
    Thank you.

  4. #4
    P45cal, The number in A1 is A2-A1 if A2 is greater than A1.
    That's the bit that was not taken into consideration....
    I look forward to hearing from you.....
    If you are familiar with celestial navigation, it will be easier to explain.
    Thank you.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Secant View Post
    In A1, I had already did the arithmetic, so this part is missing
    perhaps we need to know what the arithmetic is?



    Quote Originally Posted by Secant View Post
    If I enter 10 in A1 and 15 in A2, then C1 should return 5, not 25
    Quote Originally Posted by Secant View Post
    Second example...If I enter 280 in A1 and 5 in A2, it should return 5, not 75.
    could you show the long hand of how you arrive at these answers, because I don't see it?


    Perhaps if you explained what you're trying to do; it seems to be degrees west and east, so if you explained what these values were in real life and what result you wanted

  6. #6
    P45cal....ok, I've came up with an updated version of what I would like to see happen....here goes.......
    360 subtract (A1+A2) but the number in A1 is A2 minus A1 if A2 is greater than A1 and then subtract 360 if [(360 subtract(A1+A2)] is greater than or equal to 360 and this to return the numerical result in cell C1 with the letter w in cell D1, and if cell C1 is greater than or equal to 180, then 360 subtract the result in cell C1 and cell D1 to return the letter e.
    That's it.
    Thank you.

  7. #7
    Example for you...
    GHA is 5 degrees.....LHA is 95 degrees gives a longitude of 90 degrees east.
    GHA is 15 degrees....LHA is 10 degrees gives longitude of 5 degrees west.
    GHA is 190 degrees ....LHA is 20 degrees gives longitude of 170 degrees west.

  8. #8
    P45cal? Ate you clear now in what am saying?

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Secant View Post
    P45cal? Ate you clear now in what am saying?
    Not at all, I've tried juggling with the numbers you give and I can't get the same results.
    What you need to do, is to give say 4 examples of pairs of values, and with each example show EVERY SINGLE STEP you take to get your result; don't assume anything about me knowing this or that, pretend I'm a 7 year old that you're giving instructions on how to handle the 2 values you give him, to get the value you want. In the calculations you describe there are at least 2 IFs, so make sure that among the examples you give, each of those IFs go both ways.


    Another way to go about this is to provide a spreadsheet; presumably you can do this calculation with lots of little steps (using separate cells for each step) in Excel, so do this and test it gives the right results, then share the sheet and I will try and get the formulae down to one cell. If you choose to do it this way, then make sure you start (and highlight which these are in the sheet) with the totally raw data (no 'but the number in A1 is A2 minus A1 if A2 is greater than A1' palaver - include that in the steps) and highlight the cells where the final results are.

  10. #10
    Ok, i have got it to work perfect by using step by step cells.
    I have tested it, and all possible two variable numbers return the correct result with the correct sign.
    I will post the sheet with the raw data, and highlights as stated.
    If am able, I will post it tonight, otherwise tomorrow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •