Apportion a number over empty cells

{ 10 Comments }

Assume a matrix like data layout where Site’s are mentioned in range C2:C15 and first day of every month from January 2011 till April 2012 are mentioned in range D1:S1.  In range D2:S15, the numbers represent the value of the electricity bill received in that month.  Some cells can be blank representing bills not received in that month.

One may want to apportion the value of bills received equally over months for which bills were not received.  The assumption in doing so is that if a cell in empty i.e. bill not received in the month, then it will be included in the next bill.  The problem also is that there can be a variable number of empty cells between various bills received.

You may refer to the “Result” sheet in the this workbook.

Leave a Comment

Your email address will not be published.

*

  • Hi,
    Further to your reply in Microsoft Community forum, pleas post your solution for spreading the value over a period on a monthly basis:

    Please find below the link for sample file:

    https://skydrive.live.com/view.aspx?Bsrc=Share&Bpub=SDX.SkyDrive&resid=FFC61FD15D1AC8A1!142&cid=ffc61fd15d1ac8a1&app=Excel&authkey=!Al-BMhmF8w4KxLA

    The spreading should be on a monthly basis. e.g. the monthly rent of 1000 should be same for June and July for full month though these months have 30 and 31 days respectively.

    In case of of part of the month, it should be based on numbers of days the amount represents for that month.

    Regards,
    Suresh

      • Hi,

        Thanks for solution.

        e.g. in row 3, the monthly amount for April and May 2012 is USD 36,000. But the result gives 35,410 and 36,950 respectively which are not correct.

        Please check and suggest.

        Regards,
        Suresh

        • Hi,

          Those results are absolutely correct. There are 30 days in April and 31 days in May. Between April 1, 202 and May 31, 2012, there are 61 days. So for April 2012, the amount should be calculated as =72000/61*30=34509.84. For May 2012, the amount should be calculated as =72000/61*31=36590.16