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.

1. Manish

Wonderful solution. It served my purpose. Great!!!!

2. Suresh AK

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

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,

You may refer to my solution in this workbook.

• Suresh AK

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.

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

• Suresh AK

Hi,

I understand. But, it should be based on 30 month days for full months? Possible?

Regards,
Suresh

• Hi,

Try the solution in this workbook.

3. Suresh AK

Hi,

Great!!! It works....

Thanks a ton.

Regards,
Suresh