# Formula to Calculate Number of Appointments remaining within a period of time!?!?!

1. ## Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hey Guys,

I have attached a spreadsheet, where I am trying to use the Grid at the top as a basic Diary. All that will hold is the number of appointments ALREADY booked in on that day.

Then what I want to do is...using the Data Validation in-cell drop downs C22, C23, C24, C25 To calculate how many appointments we have left depending on how many appointments per day the person does, how many days into the future we want to know the figure for, MINUS the appointments already booked.

For example:

1. Look at the spreadsheet, there are already numbers in the grid, representing appointments that have already been booked.

2. If I select 1 in Cell C22 to signify the "1st day of the month",

3. Then, select an SLA Period for example 5 days in Cell C24,

4. Then Select The number of appointments the person will do per day, for example 5 In Cell C25.

Using my own maths, there are 92 appointments booked on days 1-5 of the month for all the people already. At 5 appointments per day, for 5 days, for 10 people, there is a possible 250 appointments, minus the 92 already booked means we have 158 appointments available to offer in that 5 day period.

I want a formula that will use the drop downs to calculate this automatically. It needs to be able to take any permutation of 1-31 days, 1-14 day SLA, 1-10 appointments per day. Also, on the 28th, if I selected a 14 day SLA, there is only 4 days left before then...I need the formula to just stop at 31, even if the SLA would take it to cells off the end of the grid.

I have tried a MASSIVE nested IF FUNCTION, which would no doubt work, but is more than 64 levels of Nesting and more than the 8,000 odd characters that excel will accept in a formula!!!

I hope I am making sense here. Obviously any questions PLEASE ask me I am desperate for your help!

HELP ME PLEEEEEASE!

Regards,

Chris

2. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

I am not sure if I got it completely, but I came up with this that reduces your nested IF down, and subtracts from the total appoints to yield your 158 with the parameters on the sheet now..

=C24*C25*ROWS(CALENDER)-SUM(INDEX(CALENDER,1,C22):INDEX(CALENDER,ROWS(CALENDER),C22+C24-1))

Is there anything else to add?

3. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

NBVC!!! You are a hero! So Simple...you can tell i'm a bit of an excel novice!!!

That's been such a great help checked all the maths and yes it works perfectly.

There is one more thing I was hoping we might be able to work in there somewhere...You see the drop down which currently said "Nationwide"???

I was wondering, If you look in column AS on the spreadsheet, I have classified the surveyor into a geographical region. If I wanted to work out the availability in say the South East. Is there a way to just add up the appointments currently booked for just those South East Surveyors, and subtract them from the maximum just those surveyors could do?!?!?

Or am I asking too much!?

Chris

4. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Do you mean this?

``Please Login or Register  to view this content.``
this formula is an array formula, though, and needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER

5. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

You are a legend!

I can reverse engineer what you've given me for when I move things around to make it look "prettier" etc! Don't know what I'd do without you! Great Help! I may have to get in touch directly with you again in future should I need any help if you're okay with that?!

I do have a few questions...If I insert a new line to add more people into the "Calender" will that just update the formula or will it skew it off?!

Chris

6. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

If you extend the formulas to include Row 15 (Totals Row), then the formula will auto-update if you insert rows above that Totals row... and as long as you don't put anything in column AS at the Totals row, the formula will be fine.

7. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Awesome!

Another quickie, how can I use an IF statement to colour a column of the calender grey?

Trying to make it so that you can clear the M, T, W, TH etc above the dates off each month, archive the old version, match up the new months days to the right date, and it will automatically colour saturday and sundays columns grey automatically?!

Thanks again!!!

Chris

8. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Select the table starting from D3, invoke Conditional Formatting|New Rule and select use a formula to determine which cells to format.

Enter conditional formula: =D\$3="S"

click Format and choose gray from Fill tab.

9. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Thanks for that!

Sorry to bug one last time, but from what we did earlier with the calender etc. What would the formula be to use the drop downs etc. but to just give us the answer of how many appointments were already booked within that period, rather than the remaining slots free?

Thanks soooo much!

Chris

10. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

If I understand correctly... just the SUM() part

e.g.

=SUM(IF(ISNUMBER(INDEX(CALENDER,1,C22):INDEX(CALENDER,ROWS(CALENDER),C22+C24-1)),IF(\$AS\$5:\$AS\$14=C23,INDEX(CALENDER,1,C22):INDEX(CALENDER,ROWS(CALENDER),C22+C24-1)))

CSE confirmed again.

11. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hi NBVC!

I'm back again!

I've started filling out the table and have noticed some imperfections in our maths.

I have uploaded the version with my numbers in for you to see what I mean.

If we look in N7:N15 we can see that BRYN HUGHES has 7 appointments booked. The calculator at the bottom should be showing me at 5 surveys per day, for 5 days, from the 11th how many are left. The maths of this should be 5*5=25, Minus the 7 booked is 18. But the calculator is showing me 25 still? which is incorrect?!

Also, I was wondering, is there a way, that we can adapt this yet further to take into account weekends. By this I mean, if i'm counting 5 days with a weekend in the middle. I want it to skip out those colums headed with "Sa" and "Su" and move on, so 5 days from thursday would actually be Thursday, Friday, Monday, Tuesday, Wednesday.

Any way we can make this happen?! You'd be a life saver if we could?!?!

Thanks Again!

Chris

12. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

The formula in AH32 needs to be confirmed with CTRL+SHIFT+ENTER.

To skip Sa and Su, perhaps add another couple of helper formulas.

In AH30:

``Please Login or Register  to view this content.``
This one is in case the Start Date is on a weekend, it will move it up to next "business day"

in AH31:

``Please Login or Register  to view this content.``
This one adds the SLA as number of "business days" (i.e. skips Sa and Su)

and in AH32:

``Please Login or Register  to view this content.``
This one needs to be confirmed with CTRL+SHIFT+ENTER and is your capacity

and in W32:

``Please Login or Register  to view this content.``
for "All"

13. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hi NBVC!

At your door once again. As you can see I have added the current Date in Cell C2, and then used tghe TEXT function to just show me the day in white text so is hidden in Cell D2.

What I now am looking to do is to have a formula in Cells AN5-AN15 which will read the current DAY of the Month, then:

1. work out how many days left until the end of the month where the surveyor has LESS than 4 surveys,
2. Divide the figure in cells AM5 by the number of days after todays date with less than 4 surveys booked, to give me the number per day required to meet their "Quota"

Regards,

Chris

14. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Can you give example results and explanations for both requests?

15. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hi NBVC!

Well as you can see by the file you have attached to the reply, in "ANDY EDDEN"'s row, we can see that from Day 17 (TODAY) onwards, he has 7 Days with less than 4 surveys booked. Then we can see that there is a figure of -21 in cell AM5 which is the number of surveys he has left to do that month. So what I need is a formula which automatically works out how many days with less than 4 after the current day (rather than manually working it out) and then do the division.

So in this case I can calculate myself that ANDY EDDEN only needs 3 more surveys per day on those 7 days to break even. (21/7=3). The only other problem is that at the moment, the 21 is a minus figure, so would need to be made positive in the calculations somewhere.

Also, if possible, I would like it to skip cells I leave blank, so if it says 0, thats a day the surveyor has free, just no appts. booked yet. If it is blank completley, the surveyor is not working that day.

Does that make sense?

Regards,

Chris

16. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Try:

in AN5:

=COUNTIF(INDEX(\$D5:\$AH5,DAY(\$C\$2)+1):\$AH5,"<4")

in AO5:

=ABS(AM5/AN5)

both copied down.

17. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Great NBVC!

Is there any way to combine those 2 formulas into 1 formula in one cell?!

Thanks

Chris

18. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Yes,

=ABS(AM5/COUNTIF(INDEX(\$D5:\$AH5,DAY(\$C\$2)+1):\$AH5,"<4"))

19. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hi NBVC!

At your door once again begging for help! The Calender thing we set up works great, seriously can't tahnk you enough for that!!!

What I'm wondering is....I have added other Tabs which will have the same thing for each month. Is there any way that I can make it so that the SLA days will count into the start of the next month?

I realise that may mean renaming the "CALENDER" to say "CALENDER SEPT", "CALENDER OCT" etc. etc. but you will know better than I.

Any chance this is possible?

To actually demonstrate what I mean...you can see on the Tab called Sept '12, we are calculating 5 days of SLA from the 25th missing off weekends. this means I actually need to add on the 1st and 2nd columns from sheet Oct '12 as part of that 5 days? any way this is possible?

Regards,

Chris

20. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Ok, this is evolving more and more complexity.

Now I added a few more helper formulas and adjusted existing helper formulas in AH32:AH34.

Instead of creating named ranges, I decided to use INDEX functions combined with INDIRECT() function ... which indirectly references a sheetname in a cell (AH32) for the next month. To get the sheetname I used formula in AH32:

``Please Login or Register  to view this content.``
Then I find what the last weekday in the current month is with formula in AH33:

``Please Login or Register  to view this content.``
The actual start and end dates formulas for the end dates remain the same except for the substituting of named ranges.

I added excess dates to go into next month in AH36:

``Please Login or Register  to view this content.``
And then we need to find the actual start date of the next month (in case the either the 1st or 2nd is a weekend)... so formula in AH37 is:

``Please Login or Register  to view this content.``
and similarly end date for next month in AH38:

``Please Login or Register  to view this content.``
and capacity from following month in AH39 is:

``Please Login or Register  to view this content.``
and capacity from current month and next month combined is in AH40:

``Please Login or Register  to view this content.``

and the final formula in W26 is updated to:

``Please Login or Register  to view this content.``
Hope it all works.

21. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hi NBVC!

Back again...I have added a few more sheets to the workbook and a few more surveyors to the "calender" and I have noticed I get a lot of #N/A errors now and I'm not sure why?!

Also there's a few more things:

Can we make it so that in December the formulas don't try to go into the new year?

Can we make the Drop downs mirror each other on all the tabs.

Can we make it so that the SLA calculator bit displays something like "Month In Past" on all those months which have gone, i.e. only works on current or future months from today's date? e.g. if I went onto JUNE, the spreadsheet would know tha June is before this month, so displays the message. If I went onto October it would work as usual?

Also, if I went onto for example december, in the future, as the SLA Calculator is running from a date this month, just to start from day 1 of the future month, until of course, the SLA Period starts to enter that month?

Finally, the option "NATIONWIDE" on the drop down in the SLA Calculator doesn't work, can we have it so it does the same calculations, but for the whole diary rather than regions?

Hope that's not too much and I hope you can use your MAD excel skills to help me!

Thanks so much NBVC!

Chris

22. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

You are moving things around and not adjusting formulas to suit...

Your October sheet Start date starts StartDate drop down entry starts at W29 now, but your formulas are still referencing starting from W27.

You should have the template for the month with formulas in tact, then insert rows if needed, then the formulas will move with the insertion or deletion of rows.

Also, I made some adjustments to the Remaining Capacity formula to work for the NationWide option....

Let's start with you reviewing this first... then take the rest in steps....

23. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hi NBVC!

I've obviously moved stuff around and not updated the formulas as you said. A problem with adding to and building a spreadsheet I guess. I keep finding myself needing to add columns etc to it. I apologise!

I have had a look at the file you attached and I have noticed the following:

If we look at the OCT '12 sheet, and set the drop downs to 12, 5, 5, WALES.

1. Cell AH33 says 12 as the start date which is correct, but Cell AH34 says 16, which is wrong, if it misses weekends that should say 18? It appears to be forgetting to skip weekends? or have I got that wrong?

2. That gives a result of 50 in cell AH39 which is wrong. It should be: 5*5*2=50 (5 appts per day, for 5 days, for 2 guys BRYN & DAVE who cover wales) then minus 4 appts booked in BRYN's diary and 6 In DAVE HANKS' diary leaves 40. So the answer of 50 the formula is giving is wrong?

3. The IF STATEMENT in W38, was one I derived to try and work out the nationwide thing in a previous version, now probably not needed if you've amended your formula to do the nationwide thing aswell. I just want that to show the total number of appts left in BIG writing. Couldn't have a formula in there as it's a merged cell?

4. I have also noticed, that no matter what area I select, the result in Cell AH39 only ever says 0,25,50 or 75. Which is wrong. Bearing in mind they are all multiples of 25, I think this could just be the total capacity of that area without removing the booked appts? Not sure though?

Sorry it's such a test for you...but I hope it's an interesting problem for you!?

Regards,

Chris

24. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Have a look at October now. Is it okay?

I simplified some of the formulas in column AH.

AH35:

Formula:
`Please Login or Register  to view this content.`

AH36:

Formula:
`Please Login or Register  to view this content.`

AH37:

Formula:
`Please Login or Register  to view this content.`

AH38:

Formula:
`Please Login or Register  to view this content.`

and AH40 got revised a bit:

Formula:
`Please Login or Register  to view this content.`

CSE confirmed.

25. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

That's Heroic!

I have noticed something else though, and not sure if this is possible...but if I leave a day in the calender completley blank...they're off work for one reason or another. Any way that formula could ezclude blank cells too?!?! Other than that...we're spot on formula wise!

Then, is there any way we can link the drop downs so they are the same on all tabs Jan through to December?

Then, is there any way that we can make it so if the SLA period would go past the end of december into a new year...it just stops at the end of december?

Then, finally, to avoid somebody using the SLA calculator on the wrong months tab...could we make it so that if the month they are on, is before the current month (currently october of course)...in Cell W38 it just says something like "MONTH COMPLETED" instead of the remaining capacity?

Regards,

Chris

26. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Originally Posted by WaylettChris
That's Heroic!

I have noticed something else though, and not sure if this is possible...but if I leave a day in the calender completley blank...they're off work for one reason or another. Any way that formula could ezclude blank cells too?!?! Other than that...we're spot on formula wise!
Can you specify exactly what you mean? What part of the formula should be affected by this?

Then, is there any way we can link the drop downs so they are the same on all tabs Jan through to December?
Not with default Excel features. You may need VBA for that... which is out of my scope on the forum.

Then, is there any way that we can make it so if the SLA period would go past the end of december into a new year...it just stops at the end of december?
Formula in AH36 would be updated to:

Formula:
`Please Login or Register  to view this content.`

Then, finally, to avoid somebody using the SLA calculator on the wrong months tab...could we make it so that if the month they are on, is before the current month (currently october of course)...in Cell W38 it just says something like "MONTH COMPLETED" instead of the remaining capacity?
Formula in W38 would be updated to:

Formula:
`Please Login or Register  to view this content.`

These formulas may change again depending on your response to question above.

Regards,

Chris[/QUOTE]

27. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Hey NBVC!

What I mean is...if you go on the file you uploaded for me in response named "Copy of Copy of Copy of Surveyor_Stats_September_2012_MASTER v3.xlsx" ANDY EDDEN has an extended period where there are no entries in his "calender" not even a zero. This is because he is on holiday, and I did not put in the 0 as easier to view, and also the 0's would interfere with other calculations...

With the drop down's set to 15, 5, 5, the SLA Calculator gives me a figure as though andy is completley free on all those days. Could we have it so that if a cell in the calender is completley blank, it is not "counted" as free? If a surveyor is working I fill the cell as 0 which is fine. Just need to skip the blank cells.

As for linking the drop downs, no panic for now, can look into that later, thats just a useability thing if someone other than me is using it.

Thanks!

Chris

28. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Ok, I think this should do it....

Change Total Capacity formula in AH40 to:

Formula:
`Please Login or Register  to view this content.`

CSE confirmed.

And change Remaining Capacity formula in W38 to:

Formula:
`Please Login or Register  to view this content.`

Hopefully that does it

29. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

That's awesome! You're a genius! Thank you sooo much!

Does that mean you're response before last which mentions a formula to go in AH36 to stop the SLA calculator running past Dec 31st is still valid or will that not work now based on the 2 formulas you just gave me?

Also, if it does work, do I use that just on the sheet named Dec '12 or on all of them?

Thanks!

Chris

30. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

Originally Posted by WaylettChris
That's awesome! You're a genius! Thank you sooo much!
You are welcome.

Does that mean you're response before last which mentions a formula to go in AH36 to stop the SLA calculator running past Dec 31st is still valid or will that not work now based on the 2 formulas you just gave me?
Yes, it is still valid as is. It should not be affected by the changes.

Also, if it does work, do I use that just on the sheet named Dec '12 or on all of them?
It is meant to go in any sheet... as long as the references remain the same.

31. ## Re: Formula to Calculate Number of Appointments remaining within a period of time!?!?!

NBVC!

I realise it has been a while since knocking at your door! But...I have a question...

In Column AN on the NOV Sheet as an example...could we make that formula do the same thing (calculate the number of appointments needed per day by dividing the TOTAL needed in Column AM, and dividing it by the number of days left in the month with less than 4 already booked in the "Calender"), but from the DAY in the date (in cell A1) until the end of the month i.e. 30 in this case?

To explain, if we look at QUAY DUONG on the NOV Sheet, it says in column AN that he needs 3.9 per day to meet benchmark, but that's across all the days in the whole month with less than 4. However, some of those days are past, so should not be included. If we look from day 14 forward it would actually equate to 5.6 per day!

I hope that make sense? if not, let me know!

See Attached

There are currently 1 users browsing this thread. (0 members and 1 guests)