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

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

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

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

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

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

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"

Thanks in advance for your help!!!

Regards,


Chris

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

Great NBVC!

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

Thanks

Chris

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

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

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

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

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

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

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