cumulative summing of rows until a certain value is reached and returning the number of rows counted

[travelmeister2]

Dear all

My apologies for a confusing title ! I'm coming on here again as I had such a great/ quick answer last time for a different problem

I work in healthcare and having to predict my waiting list for the service I work in based on the referrals that come in each week

In the table below I have
- the week of year A
- the number of new referrals coming B in I would enter this manually
- the volume waiting C- this is for cell D3 is =D2+C3-E6
- available appointments D- I type this in manually

What i would like to do is formula for or calculate the waiting time in E

this would be for row 3 : counting down the the number of rows d3, d4 d5... etc down until the sum of available appointments equals that waiting in C3. In this case add 12 and then add 20 which gives 32. which is 2 rows IE a 2 week wait. ideally where the numbers dont add up exactly I'd like it to represent the portion of the week ie 2.6 2.9 etc rather than just be an integer count of rows if that makes sense?

I can reword this if need be it took me some time to work out how to put it down into words! Can anybody help. I'm hoping this is quite simple!

Many thanks

Andy

---

	A	B	C	D	E
1	Week	New Referrals	volume waiting	Available Appointments	Waiting Time (weels)
2	23		20	6
3	24	20	34	12	= 2
4	25	20	42	20
5	26	20	42	35
6	27	20	27	54
7		20	-7	30
8		20	-17	30

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

---

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

---