travelmeister2
New Member
- Joined
- Dec 18, 2012
- Messages
- 3
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
<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>
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>