# Thread: Understanding complex formula (Excel 97)

1. ## 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.

2. ## 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. ## 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. ## 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>

#### Posting Permissions

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