Formula to get correct % based on these scenarios

[Uzma Shaheen]

Hi All,

Im hoping you could help me with a formula that gives me the right % based on these scenarios

I have columns that have data like this

Planned Attended Unplanned Attended 11-Jun 12-Jun 13-Jun 14-Jun 15-Jun 16-Jun 17-Jun 18-Jun 19-Jun 20-Jun 21-Jun 22-Jun 23-Jun
1 1
1 1
1 1 14:00
1 1 13:00
1 1 14:00
1 1 13:00
1 1 14:00
1 1 14:00
1 1 13:00





I need a formula that will give me the following for Planned...

Scenario 1) If agents were planned to attend (That will be based on whether there is a time slotted in for a date) and they didnt attend then give a % of how many people that didnt attend
so say if 10 were planned and 8 attended then thats straight forward enough 8/10 = 80%

However if 10 were planned in and 12 people attended (There is a column where we fill in yes for unplanned guys) then that should show 12/10 = 120% (2 additional people)

If 10 were scheduled to attend and out of those 10, 8 attended but we got 2 more people who were not planned and attended, then that should show as 100%

So the aim is

If planned and not attended then give a %
If planned and not attended however we got people to fill in those gaps (based on whether there is a yes in that column) for that agent then give a %
If Not planned and we got additional people to attend then give a %

so 10 scheduled - 8 attended = 80%
10 scheduled - 8 attended from original plan - 2 additional attended or 4 additional attended then 100 or 120%
10 scheduled - 14 attended then = 140%

Hope this makes sense - i need a formula that will give me a % and count for that

Col E has unplanned but attended, C has whether they attended also and M9 onwards is the dates

In planned - i have this formula but this is wrong
=IF(ISBLANK(J9),"",IF(AND(E9="YES",C9=""),0,IF(AND(E9="YES",C9<>""),1,IF(COUNT(M9:AU9)>1,1,COUNT(M9:AU9)))))

 

[Uzma Shaheen]

Any luck - how can I post the example workbook on here if that helps?

many thanks

 

[Eric W]

I'm afraid your sample data got shifted. I suggest using one of the tools to show a section of your spreadsheet, such as the HTML Maker in my signature. You can't upload a sample workbook directly to the forum, but you can upload it to one of the file hosting services, such as DropBox, and post a link to it. But be aware that many people can't or won't download files from the internet.

Another option is to use the table tool in the Advanced options, but that can be a bit laborious.

In any event, you likely have few responders due to the unclear nature of your layout. The basic formula you're looking for is pretty simple: number attending (whether planned or not) / number originally scheduled. If you are trying to count the number of attendees on a line, with a number under each date, just use SUM(M9:AU9) to get the numerator. If you just put a YES in that column, use COUNTA (not COUNT) to determine how many cells have data in them. Even better, use COUNTIF(M9:AU9,"Yes").

Good luck!

 

[Uzma Shaheen]

Thank You Eric - really appreciate your help - hopefully you can help me and it explains better by looking at the file

ive uploaded here

https://www.dropbox.com/s/v4xomdga2p89jbb/ScoreTest.xlsx?dl=0

 

[Uzma Shaheen]

I'm afraid your sample data got shifted. I suggest using one of the tools to show a section of your spreadsheet, such as the HTML Maker in my signature. You can't upload a sample workbook directly to the forum, but you can upload it to one of the file hosting services, such as DropBox, and post a link to it. But be aware that many people can't or won't download files from the internet.

Another option is to use the table tool in the Advanced options, but that can be a bit laborious.

In any event, you likely have few responders due to the unclear nature of your layout. The basic formula you're looking for is pretty simple: number attending (whether planned or not) / number originally scheduled. If you are trying to count the number of attendees on a line, with a number under each date, just use SUM(M9:AU9) to get the numerator. If you just put a YES in that column, use COUNTA (not COUNT) to determine how many cells have data in them. Even better, use COUNTIF(M9:AU9,"Yes").

Good luck!

Hi Eric

hope your all good - were u able to open up the link?

if yes - i hope it makes sense of what im trying to do and hopefully you can help me

many thanks

 

[Uzma Shaheen]

Hi Eric - thank you so much for even responding to my message

I think i managed to get all the formulas working except 1 scenario in H26 to return "Did any agents not attend from original schedule"

Here is the link to the latest file -

https://www.dropbox.com/s/dth2umuufl...1%29.xlsx?dl=0

Ive highlighted in red what the result should be based on a scenario and the example hopefully should make sense - please be kind enough to have a look at this- its a slight tweak but i cant figure out the slight tweak needed

Thank you and i really do appreciate it