Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Understanding complex formula (Excel 97)

    Can somebody help!! I am trying to understand the spreadsheet attached, which contains a complex formula.

    Its the one in G4. I can uderstand that it is comparing the 2 times in D4 and F4 to show a comparison in terms of number of minutes. I understand, as well, that the purpose of the IF statement is because of the time going over midnight and back to 00:00.

    What I don't understand is why a sum function has been used (although I guess that this is probably not needed) & then the D4=0,0 part. What does this do?

    Also, is there a way of constructing the formula so that the *60*24 at the end of the formula is not required. Surely there is a way of formatting the cell so it's not, but I can't find it!

    I hope that makes sense! Thanks in advance.
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Understanding complex formula (Excel 97)

    I believe that this formula

    =IF(D4=0;0;IF(D4<F4;F4-D4;(1-D4+F4)*24*60))

    will give you the same results. Don't know why the sum has been used.

    The function first checks if D4=0. If the answer is true, then 0 will be put in the output cell, if false, a second if statement checks if F4 is smaller than D4; if true then F4-D4 is put in the output cell; if false, the result in the output cell is (1-F4+D4)*24*60.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Understanding complex formula (Excel 97)

    If you format the cell as hh:mm then you can get rid of the *24 and the display will format in hours and minutes.

    StuartR

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Understanding complex formula (Excel 97)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> laurencewillis

    This formula sums a number of numbers depending on some conditions. I understand it like this:

    If D4 = 0 give 0 and if D4 < F4 give the F4-D4 * 24 otherwise give the 1-(D4-F4)* 24 and then add all the given numbers. Another way to write this is as follows:

    =SUM(D4=0,0,IF(D4<F4,F4-D,1-(D4-F4))*24)

    It would be nice if you would highlight the section you want to evaluate and press F9 and Excel will evaluate that portion for you. For example if you would highlight in the formula bar the section D4=0 and press F9 it will return True if D4 is 0 and False otherwise. Be careful and highlight the whole expression such as 1-(D4-F4) including all the ( and the )

    Hope this helps.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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