Announcement

Collapse
No announcement yet.

All calculating correctly except for one

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • All calculating correctly except for one



    Hello Experts!

    I am trying to breakdown the number of people per shift into interval of 30mins. I have the formula "=SUMIFS(D$2:D$71,$B$2:$B$71,"<="&$F4,$C$2:$C$71,">"&$F4)" in column "G". Everything is calculating correctly except for 11:30am interval. The first 3 columns below are the source that is being distributed into 30-minute intervals. The 5th column (HC) is where the formula is set. The set of numbers (in ladder view) are the manual distribution I made to check if the formula is working ok. All rows are calculating correctly except for the 11:30am interval. I've been checking and I cannot see the error in my formula. All insights will be greatly appreciated. Thanks!

    Time HC Manual Total
    Start Time End Time HC 6:00 AM 0 0 TRUE
    7:00:00 AM 4:00:00 PM 14 6:30 AM 0 0 TRUE
    7:30:00 AM 4:30:00 PM 1 7:00 AM 14 14 14 TRUE
    8:00:00 AM 5:00:00 PM 1 7:30 AM 15 14 1 15 TRUE
    8:30:00 AM 5:30:00 PM 6 8:00 AM 16 14 1 1 16 TRUE
    9:30:00 AM 6:30:00 PM 1 8:30 AM 22 14 1 1 6 22 TRUE
    10:00:00 AM 7:00:00 PM 8 9:00 AM 22 14 1 1 6 22 TRUE
    10:30:00 AM 7:30:00 PM 5 9:30 AM 23 14 1 1 6 1 23 TRUE
    11:00:00 AM 8:00:00 PM 3 10:00 AM 31 14 1 1 6 1 8 31 TRUE
    11:30:00 AM 8:30:00 PM 6 10:30 AM 36 14 1 1 6 1 8 5 36 TRUE
    12:00:00 PM 9:00:00 PM 4 11:00 AM 39 14 1 1 6 1 8 5 3 39 TRUE
    12:30:00 PM 9:30:00 PM 1 11:30 AM 39 14 1 1 6 1 8 5 3 6 45 FALSE
    1:00:00 PM 10:00:00 PM 5 12:00 PM 49 14 1 1 6 1 8 5 3 6 4 49 TRUE
    2:00:00 PM 11:00:00 PM 3 12:30 PM 50 14 1 1 6 1 8 5 3 6 4 1 50 TRUE
    2:30:00 PM 11:30:00 PM 1 1:00 PM 55 14 1 1 6 1 8 5 3 6 4 1 5 55 TRUE
    3:00:00 PM 1/1/1900 13 1:30 PM 55 14 1 1 6 1 8 5 3 6 4 1 5 55 TRUE

  • #2
    Hello,

    With your file attached ... the need to re-type everything ... would disappear ...
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Hi Carim,

      I tried several times earlier, different data, all giving me error in 11:30am interval. But when I tried it in my mobile Excel, all calculated correctly. It's weird but big thanks!

      Comment


      • #4
        Glad to hear it is all sorted out ...
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Hi Carim,

          I created a new file to see if that will resolve, unfortunately, it is still the same. I checked the file I created in my mobile Excel and it is calculating correctly. Any idea what could be the issue? Still the same error at 11:30am interval.

          Comment


          • #6
            Hello,

            In order to test ... you should attach your file ...
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Hi Carim,

              Here's the sample file.
              Attached Files

              Comment


              • #8
                Hello,

                For Excel ... half an hour is actually 0.02083333

                So the calculation mismatch is only the result of your powerful PC and its internal rounding ...

                See attached for the extremely tiny correction ...

                Hope this will help
                Attached Files
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment


                • #9
                  Hi Carim,

                  Adding 0.0000001 resolved it and I applied it to all rows for uniformity. I just really find it weird that in my mobile, the original formula works fine to all rows. Anyways, I thank you so much for your time and effort. I really appreciate!

                  Thank you again!

                  Comment


                  • #10


                    Glad it is all sorted out ...

                    Your mobile is a lot less powerful than your PC ... when it comes to dealing with 16 decimals ...

                    Thanks for your Thanks ...AND for the Like ...
                    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                    Comment

                    Working...
                    X