Apportion a number over empty cells


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.

RSS 10 Comments…

 Share your views
  1. Wonderful solution. It served my purpose. Great!!!!

  2. 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:!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.


  3. Hi,

    Great!!! It works....

    Thanks a ton.


Leave a Comment

Your email address will not be published.