Calculating Percentage Based On Cell Color? 
New Excel Help Service!
We need your input to help create a new Live Online Excel Help service.
Please take our survey and let us know your thoughts!
Have a great day!
Don


Calculating Percentage Based On Cell Color?  Excel 
View Answers 
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.
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 Tutorials
Calculate Percent Change in Excel
How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this v ...
How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this v ...
Thermometer Chart in Excel
How to create a thermometer chart in Excel. This is what we want: Steps to Create a Thermometer Chart in Excel Mak ...
How to create a thermometer chart in Excel. This is what we want: Steps to Create a Thermometer Chart in Excel Mak ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...
Highlight and Sort the Top and Bottom Performers in a List in Excel
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...
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
 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
 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 (nonempty cells)
 This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
 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
 This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Highlight Cells which Contain Formulas
 This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
 This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Similar Topics
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
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 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.
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 entryeach 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 nontechnical purposes. Any help would be greatly appreciated!!
I've used Excel quite a bit, but mostly for nontechnical purposes. Any help would be greatly appreciated!!
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.
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
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
I have a training spreadsheet for work. I want to, if possible, make this easier to track.
A1 on down are names of employees
B is grade
C M are the training types
Now I have it set up under the training name ther date and I have all who are current on this training green. Anyone who is due training withing 6 months its yellow. Any training that is a year, or over due/not found are red.
What I would ultimately like to do is to have these color code automatically, if possible, instead of me having to look at every training type and date.
Is there any way to set up a formula using "Today's Date" and having it color code like the above said?
Hopefully its not confusing, if so, I'll screen shot it and show an example of what I have now.
Thanks in advance,
Jim
A1 on down are names of employees
B is grade
C M are the training types
Now I have it set up under the training name ther date and I have all who are current on this training green. Anyone who is due training withing 6 months its yellow. Any training that is a year, or over due/not found are red.
What I would ultimately like to do is to have these color code automatically, if possible, instead of me having to look at every training type and date.
Is there any way to set up a formula using "Today's Date" and having it color code like the above said?
Hopefully its not confusing, if so, I'll screen shot it and show an example of what I have now.
Thanks in advance,
Jim
Hi,
I have created a scorecard which shows a cell range c10:z13, there is cells with Green, Amber, Red and Yellow to represent the status based on a percentage.
100%  Complete (GREEN)
595%  In Progress (AMBER)
0%  Not Started (RED)
X  Did Not complete (YELLOW)
I have tried to use conditional formatting, the colour is change for the cell that contains 100% rather the cell with the color i.e Green. Theoretically I should be able to change it to a percentage from 50% and I would like the cell with the color GREEN to change AMBER etc.
Does anyone have an example on xls that I can see this type of example working. Is Conditional formatting the best way to handle this type of scenario.
Cheers
Manny Ramirez
I have created a scorecard which shows a cell range c10:z13, there is cells with Green, Amber, Red and Yellow to represent the status based on a percentage.
100%  Complete (GREEN)
595%  In Progress (AMBER)
0%  Not Started (RED)
X  Did Not complete (YELLOW)
I have tried to use conditional formatting, the colour is change for the cell that contains 100% rather the cell with the color i.e Green. Theoretically I should be able to change it to a percentage from 50% and I would like the cell with the color GREEN to change AMBER etc.
Does anyone have an example on xls that I can see this type of example working. Is Conditional formatting the best way to handle this type of scenario.
Cheers
Manny Ramirez
I'm trying to design a training matrix with colour coordination.
For example:
Epilepsy Training
Expires: 3 years
So i've got:
=B6<=TODAY()1095
and the cells turn red if date goes beyond 1095 days.
But what i also wanted to do was:
cells turn orange if theres only 1065 days left (1 month)
cells turn yellow if theres only 1005 days left (3 months)
But everytime i use the same formula as i did for the Red the new colour just supercedes that one.
Please can someone help?
Hello
Can somebody help with a formula for the below?
I have the following information, and want to calculate the percentage complete based on a value in column a.
So column a contains a task number, and column I contains the percentage complete for the task:
1 20%
2 10%
1 5%
1 100%
3 50%
2 25%
4 9%
I want a formula in a different sheet to provide the total percentage complete for each task. So, where column A = 1, bring back a percentage (so adding up all percentages for every "1" task and dividing by the number of "1" tasks.
Hope that makes sense.
Thanks in advance....
Can somebody help with a formula for the below?
I have the following information, and want to calculate the percentage complete based on a value in column a.
So column a contains a task number, and column I contains the percentage complete for the task:
1 20%
2 10%
1 5%
1 100%
3 50%
2 25%
4 9%
I want a formula in a different sheet to provide the total percentage complete for each task. So, where column A = 1, bring back a percentage (so adding up all percentages for every "1" task and dividing by the number of "1" tasks.
Hope that makes sense.
Thanks in advance....
I would like a formular to work out what percentage of cells in a range have a certain contents. the contents of the cells is preferably letters (eg 'H') as it makes assesing the information at a glance easier than numbers.
So i want a cell to show what percentage of a range contain H. If it helps the percentage value of each cell in each range is 0.45%
Cells in the range could have a variety of letters, but only one for each cell.
I'm Guessing it should start with 'countif', but my formula of =COUNTIF(B10:F19,"H") isn't adding up until after i reopen the file which is a problem as the user needs to be aware if the percentage goes too high as the information is added (I plan to use conditional formatting to change the background colour of the cell) is there a way to instantly calculate changes to the range, and automatically work out the percentage?
Any help on whether this is possible is much appreciated.
So i want a cell to show what percentage of a range contain H. If it helps the percentage value of each cell in each range is 0.45%
Cells in the range could have a variety of letters, but only one for each cell.
I'm Guessing it should start with 'countif', but my formula of =COUNTIF(B10:F19,"H") isn't adding up until after i reopen the file which is a problem as the user needs to be aware if the percentage goes too high as the information is added (I plan to use conditional formatting to change the background colour of the cell) is there a way to instantly calculate changes to the range, and automatically work out the percentage?
Any help on whether this is possible is much appreciated.
I am trying to calculate the sales growth or loss as a percentage. I have A1 (2007 Sales) B1 (2008 Sales) C1(Percentage Gain/Loss)
I need to show this in a percentage representation either positive or negative. Also, some of the values are $0.00
Can anyone advise best way to achieve this?
Thanks
I need to show this in a percentage representation either positive or negative. Also, some of the values are $0.00
Can anyone advise best way to achieve this?
Thanks
Hey there people I have an issue that I have a problem with and I was wondering if anyone here could help.
My wife has been handed the task of putting together a training matrix for all of the nurses on their team, the idea behind this matrix is that it would show each individual set of skills that a nurse possesses and to highlight specifically when those skills will need updating. My idea would be to insert a date that the training took place and that the cell would highlight Green while the training is current, I would also like the Cell to highlight Amber when the training is coming close to its due date and highlight Red when it has expired. I have tried several formula that I have found on the web such as =NOW()+30 and =TODAY()+30 etc and setting colours for the various gaps that I would like...
I have three questions 
Firstly am I using the correct formula's for what I wish to do, and if not can anyone suggest an alternative?
Can the traffic light system work, as in can I set three different colours for for each cell that will change as a set date approaches?
Any further advice on this would be splendid. Thanks.
Hi All,
I have a raw of data consists of expire date. I would like the excel to help me control the first expire first out by highlighting the nearest expire date for each materials.
Note that for each materials there are several batch with different expire date. I want the excel to highlight to me which batch i should use first base on nearest expire date. And also highlight in different color if the batch had expired.
Example is attached. I had manually highlight suggest batch to use in yellow, and expired batch in red. How should i do it with formula? Perhaps with "conditional formatting"?
Hi i am working on a spreadsheet that should calculate the number of months remaining on a lease, or the number of months that have passed this is the information i have available
and what would be entered into the sheet
example?
LEASE A
start date 12/26/2009
Term : 44 months
I would like something that will tell me the number of months remaining on this lease , according to my calculations there should be 30 months remaining . what is the easist way to do this and just drag down the formula
further more, if the lease is expired or has less than 6 months left i would like it to return "expired"
Any help would be appreciated~ thanks
and what would be entered into the sheet
example?
LEASE A
start date 12/26/2009
Term : 44 months
I would like something that will tell me the number of months remaining on this lease , according to my calculations there should be 30 months remaining . what is the easist way to do this and just drag down the formula
further more, if the lease is expired or has less than 6 months left i would like it to return "expired"
Any help would be appreciated~ thanks
Is it possible to change the size of the percentage sign that Excel inserts
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.
Hello,
In cells A1, A2 and A3, I have three values formatted in percentage format. In cell B1, I type in a formula to calculate the average of the three, and it automatically gives the result in percentage format. I want this as a general number so I change it manually. The problem is that everytime I change the cell reference in the formula of B1, Excel automatically reverts back to the percentage format.
How can I stop this from happening? Thanks.
In cells A1, A2 and A3, I have three values formatted in percentage format. In cell B1, I type in a formula to calculate the average of the three, and it automatically gives the result in percentage format. I want this as a general number so I change it manually. The problem is that everytime I change the cell reference in the formula of B1, Excel automatically reverts back to the percentage format.
How can I stop this from happening? Thanks.
Example: I want to add 10% to the value of each number in rows A1 through to
A500. Each cell contains a different value. (ie. 5, 6.75, 3.15 etc). I know
how to add a percentage to a single cell but is there a way you can add a set
percentage (10%)to several hundred cells at once without having to do them
all individually? Your help is very much appreciated.
A500. Each cell contains a different value. (ie. 5, 6.75, 3.15 etc). I know
how to add a percentage to a single cell but is there a way you can add a set
percentage (10%)to several hundred cells at once without having to do them
all individually? Your help is very much appreciated.
I have a sheet that calculated percentages. I want to set up a conditional format color scale where 0% is green, 50% is yellow and 100% is red.
However, every time I use the threecolor scale and percents, it applies to color scale based on the range of values present, not the full 0100 range. So for example, if I have three cells with 5% 15% and 30%, then 5% will be green, 15% will be yellow and 25% will be red. If it were working in the way I would like, they would all be various shades of green and yellow green. I don't want to compare the values to each other, I want them compared to the range 1%100%. Any ideas?
Also, can you do a color scale that changes the font color and not the cell color?
Thanks!
However, every time I use the threecolor scale and percents, it applies to color scale based on the range of values present, not the full 0100 range. So for example, if I have three cells with 5% 15% and 30%, then 5% will be green, 15% will be yellow and 25% will be red. If it were working in the way I would like, they would all be various shades of green and yellow green. I don't want to compare the values to each other, I want them compared to the range 1%100%. Any ideas?
Also, can you do a color scale that changes the font color and not the cell color?
Thanks!
Ok. Here is the situation. I have two columns that I need to calculate the percentage change..
Column G is year 2010
Column F is year 2011
Column I is percentage change from G to F (cell is formatting for percentage)
The values in G and F can range from .5966 to 1.56, including a value of 0.000
If I use the forumula (F1g1)/g1. F1=1.4155 and G1 = 0.000 I will receive a #div/0 error.
If the values for F1=0.000 and G1 = 1.4155. The result will be 100%
I need the forumula to work both ways with the possibility of 0 being used in either column...
Any ideas?
Column G is year 2010
Column F is year 2011
Column I is percentage change from G to F (cell is formatting for percentage)
The values in G and F can range from .5966 to 1.56, including a value of 0.000
If I use the forumula (F1g1)/g1. F1=1.4155 and G1 = 0.000 I will receive a #div/0 error.
If the values for F1=0.000 and G1 = 1.4155. The result will be 100%
I need the forumula to work both ways with the possibility of 0 being used in either column...
Any ideas?
Hello,
I need help with calculating completed tasks that appear on random rows. I'm already using the formula =COUNTA(XX:XX)/COUNTA(XX:XX) but need it to automatically recognize which range of cells to apply the formula to. In the attached file, the projects are bolded and color filled. Column G calculates the nonbold/noncolor filled cells below it that are completed which is indicated by an "X". The number of tasks below each project varies and currently we need to copy the formula and manually indicate which cells to calculate. Is there a formula or VBA code that can determine which of the following cells to calculate based on color fill or bold font? Any other ideas to make this work simpler without having to change the cell range for each project? I hope I explained my question well enough.
Once again I must humbly come to the great minds for assistance.
I have a spreadsheet that I am setting up to track training dates. Using Vlookup, it will return the date of training in each of many categories for each employee. Some employees will not require some of the training, so the date cell will be blank. But on my tracking sheet, if the Vlookup finds no date it fills the cell with 1/0/00, and I need to be rid of that.
I considered using conditional formatting, but I am already using that to identify training that is about to expire. If the date returned is within a month of expiring (annual training) then it will change the font to purple, and if it is within two weeks of expiring then the font will be red. So, when I tried to make any 1/0/00 entry change to a white font to make it invisible, it just doesn't work. This is the Vlookup I am using:
=VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)
Any assistance with this would be greatly appreciated. Thank you!
I have a spreadsheet that I am setting up to track training dates. Using Vlookup, it will return the date of training in each of many categories for each employee. Some employees will not require some of the training, so the date cell will be blank. But on my tracking sheet, if the Vlookup finds no date it fills the cell with 1/0/00, and I need to be rid of that.
I considered using conditional formatting, but I am already using that to identify training that is about to expire. If the date returned is within a month of expiring (annual training) then it will change the font to purple, and if it is within two weeks of expiring then the font will be red. So, when I tried to make any 1/0/00 entry change to a white font to make it invisible, it just doesn't work. This is the Vlookup I am using:
=VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)
Any assistance with this would be greatly appreciated. Thank you!
Hi,
I work at a University and I am trying calculate the students ranks based upon their GPA. I understand how to use the Rank function to determine their numerical ranking, however I need to find out each students percentage as they stand in their class.
For Example, the student with the highest and best GPA is the Top 1% of his or her class. Is there a way for Excel to automatically calculate their percentage ranking for each student, rather than having to type it in manually?
My current approach is do a percentile function 'PERCENTILE($X$186:$X$320,.99)' which will give me a GPA corresponding to that Percentage. I then have to take that GPA and manually type in the 1%, 2%, 3% and so on until I have inputted the % Ranks for each student. There must be an easier way, correct?
Thanks for your help!
Tony
I work at a University and I am trying calculate the students ranks based upon their GPA. I understand how to use the Rank function to determine their numerical ranking, however I need to find out each students percentage as they stand in their class.
For Example, the student with the highest and best GPA is the Top 1% of his or her class. Is there a way for Excel to automatically calculate their percentage ranking for each student, rather than having to type it in manually?
My current approach is do a percentile function 'PERCENTILE($X$186:$X$320,.99)' which will give me a GPA corresponding to that Percentage. I then have to take that GPA and manually type in the 1%, 2%, 3% and so on until I have inputted the % Ranks for each student. There must be an easier way, correct?
Thanks for your help!
Tony
I'm trying to make my spreadsheet display over 100% while calculating a long column of entries. Each entry is showing percentage cost per hour of a benchmark of $65.00 per hour. When an entry for example is $51.10 the percentage displays 0.79%, but if the entry is for example $73.89 then the displayed value is 1.14%.
How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?
Thanks
How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?
Thanks
Hello,
I have searched all over and can't seem to find anything or figure this out myself. I have a sheet (example is attached) that lists multiple jobs and their related tasks. I want to know if it's possible to create a conditional formatting that says if the percentage complete under the "task" is under 100% and within X number of days of the target date, it's red/yellow/green.
So, if a target date is 70100 days out; 0%10%  RED, 11%45%  YELLOW, 46%100%  GREEN
If a target date is 4069 days out; 0%45%  RED, 46%75%  YELLOW, 75%100%  GREEN
If a target date is 2040 days out; 0%60%  RED, 6190%  YELLOW, 91%100%  GREEN
If a target date is 020 days out; 0%85%  RED, 86%95%  YELLOW, 96100%  GREEN
If a target date has passed, Anything under 100% is RED.
Hopefully I've explained this well enough. Any help is greatly appreciated!
I use the following formula to calculate a percentage based on two variables. I need to modify it so that the maximum percentage never exceeds 100%. Any help would be appreciated.
=IF(AND(M12"",N12""),N12/M12*100,"")
=IF(AND(M12"",N12""),N12/M12*100,"")