Calculate Remaining Hours

Hi All!

Hope everyone is well.

I've been trying to work out the best way to design this table but it's doing my head in so any thoughts on design/solutions would be appreciated.

Purpose: I have to accrue 1114 clinical hours for my medical internship, so i'm doing up a spreadsheet to keep track of how many i have done and how many are remaining.
Goal: Have the 'Hours this Week' column after Aug 29th display the average number of hours required each week to hit the target of 1114 total.

Inputs: Each week I will be editing the 'Hours This Week' field to reflect that week's hours
Output:
1) 'Hours this week' - i need the remaining cells in the column to display the hours required to achieve the goal of 1114
2) 'Hours Remaining - Week Close' I need this to reflect how many hours remain (can be negative if the target has been achieved).

I've tried using =$R22/COUNTIF($R23:$R55,">0") for example, which would replace the cell values for the remaining 'Hours this week' however I don't think this is the best method.

PS: I tried adding attachment however the icon doesn't reveal an attachment window?

Any thoughts would be appreciated!

Hours Accumulated Hours Remaining
Date Week Open Week Close Hours This Week Week Open Week Close
18-Apr-16 470 482 12 644 632
25-Apr-16 482 492 11 632 622
2-May-16 492 505 13 622 609
9-May-16 505 520 15 609 594
16-May-16 520 534 15 594 580
23-May-16 534 550 16 580 564
30-May-16 550 562 12 564 552
6-Jun-16 562 578 16 552 537
13-Jun-16 578 591 13 537 524
20-Jun-16 591 592 1 524 523
27-Jun-16 592 601 9 523 514
4-Jul-16 601 616 16 514 498
11-Jul-16 616 635 19 498 479
18-Jul-16 635 650 15 479 464
25-Jul-16 650 660 9 464 454
1-Aug-16 660 672 13 454 442
8-Aug-16 672 685 13 442 429
15-Aug-16 685 698 13 429 416
22-Aug-16 698 710 13 416 404
29-Aug-16 710 723 13 404 391
5-Sep-16 723 735 13 391 379
12-Sep-16 735 748 13 379 366
19-Sep-16 748 761 13 366 353
26-Sep-16 761 773 13 353 341
3-Oct-16 773 788 15 341 326
10-Oct-16 788 803 15 326 311
17-Oct-16 803 818 15 311 296
24-Oct-16 818 833 15 296 281
31-Oct-16 833 848 15 281 266
7-Nov-16 848 863 15 266 251
14-Nov-16 863 878 15 251 236
21-Nov-16 878 893 15 236 221
28-Nov-16 893 908 15 221 206
5-Dec-16 908 923 15 206 191
12-Dec-16 923 938 15 191 176
19-Dec-16 938 953 15 176 161
26-Dec-16 953 968 15 161 146
2-Jan-17 968 983 15 146 131
9-Jan-17 983 998 15 131 116
16-Jan-17 998 1013 15 116 101
23-Jan-17 1013 1028 15 101 86
30-Jan-17 1028 1043 15 86 71
6-Feb-17 1043 1058 15 71 56
13-Feb-17 1058 1073 15 56 41
20-Feb-17 1073 1088 15 41 26
27-Feb-17 1088 1103 15 26 11
6-Mar-17 1103 1118 15 11 -4
13-Mar-17 1118 1133 15 -4 -19
20-Mar-17 1133 1148 15 -19 -34
27-Mar-17 1148 1163 15 -34 -49
3-Apr-17 1163 1178 15 -49 -64
10-Apr-17 1178 1193 15 -64 -79
17-Apr-17 1193 1208 15 -79 -94

can you upload the file?

There we go, hopefully that has worked

Okay

I have had a little play with this.

I did not complete your formula as I think that there is a better way. What I have done is removed all data that is an estimate and I have created a box on the side that will tell you how many weeks you have left and the average hours PW needed. This will change each week depending on the date and how many hours you have entered.

To work out how many weeks left, I have used this formula =COUNTIF(A3:A55,">="&TODAY()) which will only count weeks if that week is today or later. It uses the Today function so every time you open the sheet, it will change as it references the actual date.

Then to work out how many hours remaining, I have put in this formula =VLOOKUP(TODAY(),$A$3:$F$55,6,TRUE)/I3 which should work as it will find the nearest date to today and use the weeks close column

If this does not work you can always replace that formula with =F43/I3 with F43 being the last row of the worksheet in the Week Close column.

Let me know how this looks to you. happy to try again if anything is not working

Thanks for your reply and time Peter,

I hadn't thought of doing it that way;

Thanks again!