Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing nested IF statements gives strange answer

    Excel 2010:

    I have 14 rows that contain this IF statement relative to the row:=IF(C8=Holidays!$D$6,"9:00",IF(C8=Holidays!$D$ 10,"9:00",IF(C8=Holidays!$D$11,"9:00",IF(C8=Holida ys!$D$12,"9:00",IF(C8=Holidays!$D$15,"9:00",IF(C8= Holidays!$D$16,"9:00",IF(C8=Holidays!$D$17,"9:00", IF(C8=Holidays!$D$18,"9:00"," "))))))))

    If the dates in column C are equal to the dates in the table Holidays, I want 9 hours inserted into cells in column N. Then I have this formula =SUM(N8:N22) to calculate the number of total "holiday hours" in cell J 30

    My problem is, the sum is way off. for example if two of the cells get 9:00 hours inserted the sum will be 432. Why does adding 9 + 9 give me 432?
    timesheet.jpg

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It would be helpful to see the formulas and results, could you attach a sample workbook?

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In working with this, I am finding it has something to do with using the h:mm format. If I change the formatting to numbers, all is good.

  4. #4
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't see a way to insert/attach a file.

  5. #5
    Star Lounger
    Join Date
    Feb 2010
    Location
    Rocky Mountains
    Posts
    63
    Thanks
    8
    Thanked 4 Times in 4 Posts
    Rather than using Quick Reply, press the Go Advanced button, and you will see a button that allows you to attach a file.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does [h]:mm format make more sense? It is elapsed time and will go over 24 hrs. h:mm is for time of day and will only be 0:00-23:59.

    To upload file see: http://windowssecrets.com/forums/sho...l=1#post955184

    Steve

Posting Permissions

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