Percentage formula that does not include blank cells

[Bryony Lloyd]

Hi there,
My name is Bryony Lloyd I am very excited about your forum - what a great place to exchange information and tips about excel. I am quite a beginner and I'm working on complex databases with complex formulas so I'd really appreciate (and would be very grateful for) some help!
I have uploaded a sample of my excel database with all confidential information removed.
Basically what I'm trying to do is create a percentage total of how many people have done a particular course. If they have not done the course that is because its is not required so that cell must not be included in the overall percentage.
At the moment, as you can see from my sample, the percentage shows 100% if there's just one date in there.
I would like to calculate the percentage formula so that the blanks are not included. I would also like to be able to calculate the percentage formula for each Hub (again so the blank cells are not included).
Any help would be very much appreciate.
With respect, love and gratitude.
Yours
Bryony Lloyd

 

[Khalid NGO]

Hello Bryony Lloyd, warm welcome and thanks for joining the forum

See if this works:
=COUNTIF(D7:D14,">"&NOW())/(COUNTA(D7:D14&"")*100)
Copy to right

Or this since you are using the Table:
=COUNTIF(Table1[E&D],">"&NOW())/(COUNTA(Table1[E&D]&"")*100)
Drag to right

Regards,

 

[Bryony Lloyd]

Dear Khalid NGO,

Thanks SO much for your help. I have tried the formula's you suggest. They don't quite work and I'm not sure why. Perhaps you would be kind enough to look at the attachment again? In the E&D column I tried the first formula and in the 'Fire Safety' column I tried your second suggestion.

Yours with kind regards,
Bryony

 

[David Evans]

Hi @Bryony Lloyd -

@Khalid NGO may be away for a while, so let me see if I can help you resolve this issue ...
In cell D4 you appear to have several formulas - I cleaned them up for you, and it appears to work - what answers do you expect from each of the cells d4:f4?

 

[NARAYANK991]

Hi ,

I am confused by your problem description.

1. There are 7 names in column A , in the range A7:A13.

Are all these names unique or will names be repeated ?

2. If we take a course like E & D , there are 3 dates filled in.

You say that where ever there is a blank , it means the names were not meant to do that course , and hence they should be excluded from the calculation.

Does this not mean that in every case , the result will be 100 % ?

This will also be true for the hub-wise calculation.

Can you clarify ?

Narayan

 

[Bryony Lloyd]

Hi @Bryony Lloyd -

@Khalid NGO may be away for a while, so let me see if I can help you resolve this issue ...
In cell D4 you appear to have several formulas - I cleaned them up for you, and it appears to work - what answers do you expect from each of the cells d4:f4?

Hi David, thanks very much for your help with this. I am expecting D4 to F4 to show a percentage of the total. So that if all the cells have a date in them it shows 100%. The date shows that the person has done the training and the date is when they will become not compliant with that training (ie it needs updating). If the cell is blank the person does an admin role for example and does not need the clinical training at all. I want the percentage to show who is compliant (within date) with the remainder of the people being not compliant (the date is before today) and for the formula to only include the cells with a date in them.
Hope thats clearer?! I realise this is very complicated and tricky and its hard to explain. I'm not even sure if excel can do what I want it to do!
Let me know if I can clarify further?
A million thanks!!
Bryony

 

[Bryony Lloyd]

Hi Narayan,

Thanks so much for your reply. I know its very confusing! And I apologise if I'm unclear. I'm still quite a beginner at excel but learning fast!

See below for my responses to your questions.

Thank you thank you thank you!!
Bryony

Hi ,

I am confused by your problem description.

1. There are 7 names in column A , in the range A7:A13.

Are all these names unique or will names be repeated ?

The names are unique.

2. If we take a course like E & D , there are 3 dates filled in.

You say that where ever there is a blank , it means the names were not meant to do that course , and hence they should be excluded from the calculation.

Does this not mean that in every case , the result will be 100 % ?

No because the formula is dependent on the date. If the person has done the training the date shown is the date when the training requires an update. So if the date in the cell is before today the person is non compliant with required training. If the person is in an admin role and doesn't require the clinical training the cell is blank. Does this make more sense - I'm happy to keep explaining it until we find out how to do it! It has also been suggested to me that I use pivot tables to get the percentages. I'm not sure which is the best way yet!

This will also be true for the hub-wise calculation.
Can you clarify ?
Narayan

The hub wise calculation is simply to find if people at a certain clinical base are compliant so that the formula (if possible) could include only the people at that hub who have done the training. Again, perhaps a pivot table would be better?

Hope this helps! Many thanks and kind regards, Bryony