I am trying to create an annual leave calender with a cell at the bottom that states the percentage of staff on annual leave per day as we cannot have more than 20% staff off at one time. ex - Monday - 4 people off from a staff of 30 people new to excel so any suggestions how to do this would be good
Last edited by tfograham; 07-22-2015 at 06:15 PM.
If you have a list of people down column A, and a list of dates across row 1, with "Annual Leave" entered into the row for the person and the column for the date, then at the bottom of the column - say, column B, use a formula like
=COUNTIF(B$2:B$31,"Annual Leave")/30
Formatted as percentage.
If you have varying numbers of people over the course of the year, then you may need to use a formula like
=COUNTIF(B$2:B$31,"Annual Leave")/COUNTAF(B$2:B$31)
which would work if you have an entry in every cell of the column.
Bernie Deitrick
Excel MVP 2000-2010
This is what I am working from.p6PCbG7.png
in the % row type this formula:
=e9/30 copy across
If I've helped U pls click on d *Add Reputation
Thanks for your help it is working how i expected now.Originally Posted by bhenlee
great. please mark this thread as Solved if you're satisfied with the solution.
Hi try this one which I have taken an extract of from one used at work and added in the %
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks