Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Calculating Percentage Based On Cell Color?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have another task that I have been asked to add to my wonderful training spread sheet. As personnel expire on their annual/biannual/semiannual training the cells now turn red.

Is it possible to calculate a percentage off of this? I need to know a percentage of how many cells are expired or red in the spread sheet. That way I don't have to keep manually calculating a "readiness" percentage everytime they ask for it.

So lets say 10 cells are red (which meand they have expired) and the remaining 130 cells are either green or yellow (which means they are not expired), then we are roughly at 93% ready or complient on training.


Similar Excel Video Tutorials

Helpful Excel Macros

Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics







I use conditional formatting on a training spreadsheet to change the colors of all cells (based on the actual training date entered into the cell) to green (trained) for due dates that are a year away, yellow (training required soon)for due dates that are 45 days away, and red (training required now) for due dates that are 30 days or less away. I then manually count the number of green cells and yellow/red cells to come up with my "trained" number and "required training" number, and then let Excel do the math ((RT/T)*100=%) to come up with a percentage for overall training complete. Is there a formula or other method that can look at the actual colors of the cells in a column to automatically calculate the percentages in a cell at the bottom of the column?

Thanks in advance for any help!

Excelearner


I need to create some conitional formats, but don't know how. I am used to excel 2007 edition (wasn't a pro by any means) and dont have a clue how to do things on 2010.

I have a spread sheat that tracks annual training and competency for the staff at my work. There is training that is due annually and some due every 2 years. What I am looking to have happen is that I want the cell to turn yellow 30 days before the due date and red when it is expired. Any idea how to set this up? What I will have is the date that the current training was done on.

I.E. Bob did his training on 04/25/2010 so on 03/25/2011 the cell would turn yellow and 04/26/2011 the cell would turn red unless I put in a new date.

Any help would be appreciated!


Is there a way to calculate a percentage based on these conditions
I need to figure a formula to calculate if Product A is sold and the cost is between 199-208, to pay the commission out at 7.5% of this cost.. so if it was sold for 205 then at 7.5% of this cost the amount that is payable as commission would be $15.38, if it was sold for 219 then at 11% of this cost is payable as commission would be $24.09.. etc

To make this even more complicated, I would like the formula to also determine if the customer status is Active (not cancelled) and the comm status is not Expired.

The old way to pay this out was just a flat rate based on the product, if they were Active and if they were not Expired then:

'=IF(OR(I20="NOT-ACTIVE",G20="EXPIRED",G20="REMOVED"),0,IF(OR(D20="PRODUCTA",D20="PRODUCTB"),30,IF(OR(D20="PRODUCTE",D20="PRODUCTH",D20="PRODUCTD"),15,IF(D20="PRODUCTF",20,IF(D20="PRODUCTG",10,)))))

COLUMN D COLUMN G COLUMN I COLUMN Q
PRODTYPE-D20 COMMSTAT-G20 CUSTSTAT-I20 RENTINCOME-Q20
producta expired active 205
productb not-expired active 260
productd not-expired active 115


This is my list of of possible combinations.
If Product A
cost is 239 payable percentage is 15% of cost
cost is 229 payable percentage is 13% of cost
cost is 219 payable percentage is 11% of cost
cost is 209 payable percentage is 10% of cost
cost is 199 payable percentage is 7.5% of cost

If Product B
cost is 289 payable percentage is 15% of cost
cost is 279 payable percentage is 13% of cost
cost is 269 payable percentage is 11% of cost
cost is 259 payable percentage is 10% of cost
cost is 149 payable percentage is 7.5% of cost

If Product C
cost is 319 payable percentage is 15% of cost
cost is 309 payable percentage is 13% of cost
cost is 299 payable percentage is 11% of cost
cost is 289 payable percentage is 10% of cost
cost is 279 payable percentage is 7.5% of cost

If Product D
cost is 139 payable percentage is 15% of cost
cost is 129 payable percentage is 13% of cost
cost is 109 payable percentage is 11% of cost
cost is 99 payable percentage is 10% of cost

This is an example list of data

COLUMN D COLUMN G COLUMN I COLUMN Q
PRODTYPE-D20 COMMSTAT-G20 CUSTSTAT-I20 RENTINCOME-Q20
producta expired active 205
productb not-expired active 260
productd not-expired active 115

This is the formula I have been able to come up with, but it is for only 1 product type. It does work but I need to be able to put all 5 product types as possibilities.

'=IF(D20="PRODUCTB",IF(Q20

I am trying to come up with an Excel 2007 spread sheet to track training dates. I want one column with training dates and the next column to the right to have either "Current" in green or "Due" in yellow, or "Expired" in red. Current would be 365 days + current date (training is good for 1 year). "Due" would be 30 days + current date (would give me 30 days to let the person take the training and complete it). "Expired" would be date less than today (training is overdue).


Its been quite a while since i have used excel, so sorry if this is an extremely stupid question. Im in the military and am currently putting together a spreadsheet to keep track of other service members training requirements. basically what i am trying to do is this:

say cell A2 has the date 12-05-10 (day month year) how can i make it so based on the current date cell A2 will automatically change color (i.e. green for training that has been completed, yellow for training that will expire within 3 months, and red for training that is expired based on the current date.)

I appologize if this is a stupid question but ive been with the infantry for quite some time and have not worked with computers for several years.

thank you in advance


I have been trying all day to get this to work. Basicly my spreedsheet has peoples names on the left and different training courses up top. I have to track when they are due for annual training. I want the cell to highlight yellow withing 10 days of the training expiring and red when they are expired. I wish the buttons just let me put in = < > signs but they dont so I need a code. Anyone help?


Thank goodness I found this! Please help

So here is my question.

I have a list of 25 employees (cells A1:A25) with an additional 5 cells reserved for future employees (A26:A30)

I have a group of 30 cells (B1:B30) that, when certain employee training criteria are met, will have the letter "x" typed into them.

Below these 30 cells is a "total" cell. What I would like for this cell to show is the total percentage of training completion of the employees listed. I would normally use =COUNTA(B1:B30)/the total number of cells, but I do not want the formula in the "total" cell to calculate the blank cells that may or may not exist in cells B1:B30.

Basically I want to be able to put an x in those cells and get a percentage of those cells containing an x without counting blank cells.

Example:

John x
Sally
Jeff x
Mike x
"blank"

Total
%

Can you help me?

Thanks

Brian Aragon


I am trying to create a formula that looks at a spreadsheet containing training percentages for employees. I need to look at these percentages and based on the date that the person started I need to look at certain rules. First it needs to look at what has happened more recently , moving to a new department or promotion. Next it needs to use that info to decide which rule to use. Ex. the person started 90 days ago then they are subject to rule A. The training percentage then needs to be looked at to see what rating they have. EX. percentage < 90% but > 75% gives a rating of 1. percentage than 50% gives rating of 2. percentage < than 50% rating of 3. Rule B would contain different criteria. My problem is that I tried nesting IF statements but I need more than 7. When I tried to do this in VBA I couldn't get it to work. Please help!!


Having an issue with my spreadsheet and i dont have a way to import my spreadsheet like i have seen others here but here is my delima. I have spreadsheet that has training dates for all my employees, than on another sheet i have a print-out sheet that shows the employees if they are good with traing that we can print out. Column AC7 is brought over from the training log, column AA7 is basically whited out as a reference to go ahead 1 year to tell AI7 if expired or not.

AA7 AC7 AI7

05/27/2009 05/27/2008 OK
02/18/2010 02/18/2009 OK
12/30/1900 01/00/1900 EXPIRED
#VALUE N/A # VALUE


AA7 Formula =IF(AC7="","",+AC7+365)

AC7 Formula =IF(ISNA(VLOOKUP($A$3,trainlog,35,FALSE)),"",VLOOKUP($A$3,trainlog,35,FALSE))

AI7 Formula =IF(AA7="","",IF(AA7>$AF$1,"OK","EXPIRED"))

The N/A is inputed into the Training Log sheet because some individuals dont require some of training, how could i make the values in AA7 and AAI just be blank?

I had asked earlier about my formula in AC7 and recieved 2 great answers but it messes with my dates and make AI7 say Expired when i need to have blanks.

If need be i could even email the file to you to look at.


I am trying to come up with a way to track employees training using a spreadsheet for each employee. On that spreadsheet I have all of the required training each person has for the year and their due date. What would be a good way, if any, for excel to show that a training is due? Perhaps a color change on the cell from green to yellow as it approaches 30 days then to red after the deadline has expired? Also I would need this converted to Julian calendar. Any thoughts or suggestions?

Thank you in advanced!


I have used sum product to identify how many times training has been carried out in a specific year (say 2011 - 6), which is great. However I want to demonstrate the cost of this training and track it through the decade. If I change expired training to training complete (2011) my sheet automatically identifes the new Expired date (adds 5 years-2016). the problem is that this is now removed from the original sumproduct (2011 - 5) and relisted 5 years on (2016 - 1). How do I do both? Keep the original Sum, but also track the changes through time. It is already a large sheet so would like to keep columns to a minimun.


Hello

First time poster to this board so hello all.

I need a bit of help with an excel book I am working on.

I am using excel 2007.

In columns G, H and I i have headers of Yes, No and n/a below these I have blank cells that I will insert a value (lets say an X) the X would only ever go into one column not all three. THis would repeat for each row within these columns. The X would move between yes, no and n/a. I need a celll at the bottom of the sheet that would calculate a percentage out of 100% based on the X's. For example if I had ten rows and under the yes column there were 5 X's then in the percentage box it would say 50%. I need a percentage based on the number of yes's that are populated with an X.

Hope that makes sense.

Thanks for any help that is offered


I have Excel 2007. I track training for my unit. I have a column of 69 dates of which I input. I use a conditional formula in the adjacent column to show green (current), yellow(due within 30 days) and red (overdue). Using the first column or the conditional column, I would like to get a percentage of dates in the column that are current as of today. So everytime I log in I can retrieve a percentage at a quick glance. My first column is O2:O70 or the 69 assigned personnel. Some of the columns may not have a date as new people have taken the class yet. Any help or a start with this would be great.


Please assist:

I'm pretty sure I need an "IF" formula in my process...just not sure how to structure it.

Here goes:

I have a training file that I'd like to quickly show a status of G, A, or R (color coded) depending on when course was taken.

G or Green = Good/Current, A or Amber = < 30 days before training expires, R or Red = Expired

I was able to create an "IF" for G & A, but not sure how to flag when training is within 30 days of expiration (e.g. Amber).

Thanks for your help.

Training_Template.xlsx

Hi Guys,

Good day to you!

I am trying to make a training plan and record in one workbook.

In training plan i have to put numbers, which represent for key training description and the other sheet - the training record i have to input the actual dates of training they undergone.

So, when dates will be inputted on the training record then the training plan sheet, the certain training cell will change the green color that it means he is already trained but the data on the training plan must not be changed.

I do hope you can help me on this matter.

Thank you in advance.

Regards,

Ann


I have 2 questions actually.

1. I know nothing about financial formulas. I need a formula for:
If I invest $1000.00 in a CD at 5.35% AYP, how much money would the CD's value be at the end of 12 months? How much would it make daily and monthly?

2. I have a column of dates which represent the day an employee attended training. I was trying to conditionally format the column so that the date would turn blue if it came within 30 days of this specific date a year from now to indicate that the time for re-training was close and red if it were past the date for re-training.
(Ex: 5/31/07......the date would turn red anywhere from 5/1/08 til the date and a different color if past that day - 5/31/08)


Thanks,
Don


I'm trying to calculate percentages with Excel, but I want it to ignore any blank cells. For example, I have 5 cells for data entry-each of the cells will contain a number up to 60. Therefore, the cell that is calculating the percentage of the 5 cells is based on a total of 300. However, if one of the cells is left blank, I want the the total percentage to be based on 240; if two of the cells are left blank, I want the total percentage to be based on 180, etc. I also want an entered zero to be included in the calculation (not changing the total the percentage is based on.)

I've used Excel quite a bit, but mostly for non-technical purposes. Any help would be greatly appreciated!!


Hi

I am trying to set up a spreadsheet to track expired documents or track when training needs to occur. I'd like to get the output of the name of the person and the item that has expired. I'd also like to get the total number of expired documents and number of expired documents by type. Is this possible?


I have a large spreadsheet that has over 400 people on it and over 60 columns per person. Each of those columns has dates that each person has completed a certain portion of training. The first Colum has Either a 'Yes' or 'No' to indicate if they count towards our group or if they are just temporarily attached to us administratively and do not count towards our training numbers. I need to find the percentage of personel who count towards our training that have completed a certain task. Basically I need a formula that will only count if a cell has data in it if there is a 'Yes' in the colum to the left of it. Any formula I've used so far has included the 'No's in it's calculations, so some fields have been off by over 30%. Any help would be appreciated.


Hi,

Im calculating a percentage using two different cells (i.e. the percentage A1 represents of B1) however if there is no values in the cell, a #Div/0! appears. How can i prevent this from appearing?

Thanks


Hi,

I have some problem in calculating percentage. I would like to calculate percentage below mention manor.

If I press 25% it would automatically calculate the same percentage. The same for 50% and 75%.





Thanks,
Maunesh


hi
right now i have a sheet with conditional formatting which consists of, 1-if percentage is greater than 0% make result green, and 2-if percentage is less than 0%, make result red. this works fine. what i'm trying to do is, lets say i place an 'x' in cell A1, i want all the cells on the sheet that have a positive percentage to become blank. when the 'x' is removed, the green percentage should return. any suggestions on this. thanks for any help.


Hi all,

I am needing to put together an Excel report that has to visually highlight different parts of a bunch of training statistics, and it has me stumped.

I would appreciate any help that you can provide.

At the moment, I have lots of raw data in Excel that lists the training attendance of lots of staff to a whole lot of different training events. All of the training events are 'mandatory'. Some of them are one-off, while others are annual updates.

To complicate things more, within my whole group of staff, not all training events are mandatory for all staff. There is some training that is only relevant to specific sub-groups within my team.

What I would like is a report that:
1. Is able to generate percentages of attendance for the different events, taking in to consideration that not all training is mandatory for all staff
2. Highlights in some way (change of colour?!?) the staff that are overdue for the annual mandatory training
3. Allows for easy entry of new data, as it will be a constant reporting tool

Does that all make sense?

Below is an example of what I mean:

Training Event 1 Staff Name 1 Role 1 14/09/2010 Training Event 1 Staff Name 1 Role 1 07/03/2005 Training Event 1 Staff Name 1 Role 1 29/06/2010 Training Event 1 Staff Name 3 Role 3 29/06/2010 Training Event 1 Staff Name 3 Role 3 18/05/2010 Training Event 1 Staff Name 3 Role 3 16/04/2009 Training Event 2 Staff Name 1 Role 1 16/07/2008 Training Event 2 Staff Name 4 Role 4 15/05/2010 Training Event 3 Staff Name 2 Role 2 11/02/1999 Training Event 3 Staff Name 2 Role 2 29/06/2010 Training Event 3 Staff Name 2 Role 2 07/03/2005 Training Event 3 Staff Name 5 Role 5 24/05/2005 Training Event 3 Staff Name 5 Role 5 07/03/2005 Training Event 3 Staff Name 5 Role 5 30/04/2004

Training Event 1 is an annual update and is mandatory for all staff roles. Training Event 2 is a one-off course, which is mandatory only for roles Training Event 3 is a one-off course which is mandatory for all staff What I would also like is the ability to have the percentage of attendance displayed for each training event.

Is that asking too much?

I am using MS Office 07 with Windows XP.

Any advice or help you can provide would be greatly appreciated.

Also, if what I am asking is not psosible, please let me know!


Thanks,

Norbert


I have a 2 part problem and admit to being an excel novice so I need simple step answers please.

The spreadsheet I have created has a column where the date individual staff members complete different training serials is entered. (Each row has a different staff member and different type of training listed).

Firstly, what I need excel to do for me is: in another column add an automatic expiry date for that training: however different types of training have different expiry dates ranging between 1 - 4 years. Is this possible to do and if so how?

Secondly, I need to change the colour of the expiry date cell to: green when it is within 3 months of the expiry date, yellow when it is within 1 month of the expiry and red once the training is expired.

Sorry, I know it's a lot to ask, but is it possible?
Thanks
KC


Hi,

I have a workbook that contains 2 sheets. The first sheet is my results page and contains vlookup formulas the second sheet is where my data is stored.
On my first sheet I have 3 columns one is headed 'type' the next is headed 'complete' and the last one is headed 'refresher due?'.
The vlookup refers to my data sheet and populates my 2nd column which in turn triggers my 3rd column if applicable.

Under type I have various types of traing listed as text. I have used 'countblank to determine the number of training courses that have not been completed and used vlookup to populate a cell within sheet 1.

If the 2nd column is populated with a date and triggers a refresher date some times the refresher date has expired e.g. completed 01.03.06 refresher due 01.03.07. I have used conditional formatting to highlight cell, but is there anyway I could count the number of cells that are expired. I was thinking of creating additional columns in my 'data' sheet and referencing that way, but only way I think that would work would be to highlight the cells and maybe count them that way.

I also have training that is due to expire ( 3 months) for employees would like to count that too.

My 1st sheet 'results' has a list of legends indicating what the coloured formatting refers too.


Thank you.