Results 1 to 4 of 4

20020312, 16:01 #1
 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.

20020312, 16:26 #2
 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;F4D4;(1D4+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 F4D4 is put in the output cell; if false, the result in the output cell is (1F4+D4)*24*60.

20020312, 16:33 #3
 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

20020312, 16:35 #4
 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 F4D4 * 24 otherwise give the 1(D4F4)* 24 and then add all the given numbers. Another way to write this is as follows:
=SUM(D4=0,0,IF(D4<F4,F4D,1(D4F4))*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(D4F4) 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>