Condition Formatting By Percentage?
Jun 29, 2014
I would like to format a number of cells depending on the percentage of another cell.
I have a cell 'Seats" that has a number in it. In other columns, 'Stop1', 'Stop2', 'Stop3', are vaious numbers. I would like to format the Stop cells according to how close (or over) the number is that is in the 'Seats' cell. For example, if Seats is 30, Stop 1 is 15, it would be a medium red. Stop 2 is 30 (or over), it would be red. If Stop 3 is 5, then white. Basically a percentage scale of color depending on the number in the Seats field.
View 1 Replies
ADVERTISEMENT
Apr 3, 2013
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
View 2 Replies
View Related
Feb 29, 2008
I'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
View 9 Replies
View Related
Feb 21, 2013
I am trying to figure out why my number will not format correctly... I have a number thats value is pulled in via external data that gets updated everyday.
Everything works fine there, but I needed the format of that number that was pulled in to have parenthesis around it...
I know that one can add the parenthesis to the number by using the ="("&(Number)&")" function...
What the problem is is that when I add the parenthesis to the number it will not allow me to view it as a percentage; it will leave it as a full general number (Ex. (0.0006), instead of (0.06%)
Is there a way to get the percentage number format back?
View 2 Replies
View Related
Nov 6, 2011
I was wonder if there is anyway in excel to do the following. I have a table that shows %'s,,, and I have formatted it to show 1 decimal place. Is there anyway to do it so that if the % is a whole number, IE 100% it shows just 100% and not 100.0%
So; basically, all %'s that aren't whole numbers will show 1 decimal place,, whole numbers to show NO decimal place.
I can see in excel a custom formatting under the Number tab,, but if this is where you do /can do it,, I'm not sure how.
View 9 Replies
View Related
Oct 5, 2013
I need a conditioning format formula. If cell A2 is less than 50% of cell A1, then highlight red.
View 2 Replies
View Related
Jan 26, 2014
Using conditional formatting to make all the cells that contain numbers and are formatted to percentage format make display in red color? let's say I want to set this conditional formatting rule initially after just opening the excel book and afterwards when I type in values/text etc. into the cells I want the cells where I have input numbers and chose the percentage format to come out in red color? and all the rest stay in black color?
View 6 Replies
View Related
Feb 27, 2014
Is it possible through a macro that the cells where numbers is in percentage format should not show any decimal points (is should not roundoff) just decimal points should not visible. I am aware that this can be done by "decrease decimal" but data will be across thousand of rows manually it will be very time consuming.
I have attached a sample worksheet : Percent.xlsx
View 3 Replies
View Related
Apr 3, 2009
Using conditional formatting i am trying to write a formula that changes colour on the percentage difference of another cell.
An example would be if cell A1 contained a target of 5% in cell A2 would be an actual. If cell A2 is equal to A1 or better then it would be green. If cell A2 was within 10% less of A1 (i.e 4.5%-4.99%) then amber and red for the target minus 10% (i.e <4.49%)
I want to do this as a conditional formatting formula as the target figure will change but 10% difference will remain the same.
View 5 Replies
View Related
Jun 11, 2014
I currently have a thermometer chart in Excel 2013 that I set up with percentages along the Y-Axis. The thermometers are showing spending based on a budgeted number. Currently, the project is over-budget so I have adjusted the thermometer scale to go far beyond 100%.
I would like to have the percentages past 100% show in red along the lefthand side.
View 2 Replies
View Related
Nov 20, 2013
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 70-100 days out; 0%-10% - RED, 11%-45% - YELLOW, 46%-100% - GREEN
If a target date is 40-69 days out; 0%-45% - RED, 46%-75% - YELLOW, 75%-100% - GREEN
If a target date is 20-40 days out; 0%-60% - RED, 61-90% - YELLOW, 91%-100% - GREEN
If a target date is 0-20 days out; 0%-85% - RED, 86%-95% - YELLOW, 96-100% - GREEN
If a target date has passed, Anything under 100% is RED.
View 4 Replies
View Related
Sep 29, 2011
I need to have a conditional format that will adjust based on percentage ranges.
1% to 25% would be shaded red
26% to 50% would be shaded Orange
51% to 100% would be shaded Green
I was hoping to reference another cell and have the formatted cell reflect the conditional format. I dont think that's possible, so I can include a percentage in another cell and have that highlighted.
View 9 Replies
View Related
Jan 26, 2009
I have the following code which prints the number of time steps required and displays it as t0, t1, t2 etc how can I make all the numbers subscript? i.e until the number of time steps = the total time.
View 2 Replies
View Related
Mar 12, 2014
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
View 3 Replies
View Related
Mar 1, 2010
I have a workbook (column K) set with the following format conditions:-
Cond.1 - =(I3="declined")
Cond. 2 - =(G3>0)
Cond. 3 - =(K3
View 9 Replies
View Related
Sep 13, 2009
There is a table [A8 TO D60] and a column alongside, where the months of the year are listed. In a particular independent cell, the name of any month can be chosen and entered.
The table rows preceding the chosen month, have to be shaded. When a different month is chosen, the shading should now cover the new set of rows preceding this different month and so on.
View 8 Replies
View Related
Aug 21, 2014
I am using the following code that I got from this forum to color tabs. But I can only get it to do two colors. Is there a way to get three conditional colors of tabs?
Private Sub Worksheet_Change(ByVal Target As Range)
With Me
Select Case .Range("D13").Value
Case Is < 20
.Tab.Color = vbGreen
Case Is > 20, Is < 30
.Tab.Color = vbBlue
Case Is > 30, Is < 100
.Tab.Color = vbRed
End Select
End With
End Sub
View 8 Replies
View Related
Feb 20, 2008
I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.
The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.
I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.
I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.
View 5 Replies
View Related
Aug 21, 2014
I would like to change the color of a cell to either green, yellow, or red based on a few conditions. One of the conditions would be based on whether a check box was checked or not.
View 2 Replies
View Related
Jan 6, 2010
I'm referring to a post I tried to solve:http://www.excelforum.com/excel-gene...ml#post2227328
NO need to read. Try the attachement instead.
Steps done:
Select Column D
CF function =D1<A1
Select Column A
CF function =A1<D1
If I select cell D3 and look at the CF then I see:
CF: =D1<A1
where I would like to see
CF: =D3<A3
I remember this was the case in <=XL 2003? (now using 2010 beta)
View 4 Replies
View Related
Sep 28, 2012
VBA code to disable(grey out) condition formatting command in ribbon. Because I generated a template and do not want user to change the condition formatting.
I have tried below, but it did not work...
Application.CommandBars(1).Controls("Format").Controls("Conditional Formatting...").Enabled = False
View 5 Replies
View Related
Jul 22, 2014
I have a column of numbers that are already formatted as text. Some of the data is duplicate which is fine. It's like a list of 6000 numbers. I want to conditionally format the column so that I can fill the cell with a color as long as it stays the same number, but if it changes to a new number make it a different color. I don't need a lot of colors. Two is fine. TI have Excel 2010.
View 4 Replies
View Related
Jul 27, 2009
I am now trying this using Conditional Formatting in Excell 2000; so limited to 3 conditions. However I cannot get it work with multiple conditions:-
Values;-
A34 = Heritage Rly Assn Business Development Committee
A35 = North London Rly Historical Society
C34 = 23/09/2009 (formatted as WEEKDAY DD MMM YYYY)
D34 = 23/09/2009 13:30 (formatted as HH:MM)
In Cell C34
Format condition 1:-
=A34<>A35 , Format Bottom Border
Format condition 2:-
=AND(WEEKDAY(C34)>1,WEEKDAY(C34)<7,HOUR(D34)<18,A34<>A35), Format Bottom Border and Yellow pattern
Format condition 3:-
=AND(WEEKDAY(C34)>1,WEEKDAY(C34)<7,HOUR(D34)<18), FormatYellow pattern
In cell c34 Format condition 1 activates NOT condition 2.
In another cell, C13, condition 3 correctly operates; in this row A13 = A14. Condition 1 works i.e. the cells A1 and A2 are the same only cell A2 has the bottom border and if Cell A2 and A3 are different there is a bottom border in both cells. There must be something wrong with my formula in condition 2 but I can't see what; condition 2 is just condition 1 amd 3 amalgamated.
The condition 2 should read:
If the date is not a Saturday or Sunday in cell C34 and the time is before 18:00 hours in Cell D34 and the name in the "a" cell i.e. A34 does not equal the name in the "a" cell below this one i.e. A35 put in a bottom border and colour the cell yellow.
View 2 Replies
View Related
May 10, 2013
Date Due
Date Ressolved
5/16/2013
5/17/2013
5/16/2013
5/9/2013
5/9/2013
How do I make this change, If the date resolved column has not data entry, but todays date is past the due date cloumn data entry change cell red and insert text to read "past date due"
View 6 Replies
View Related
Nov 26, 2009
Is it possible to check the conditional state for a specific condition for a specific cell.
For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc.
The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions)
So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address.
To msg the user where data entry is still required etc.
View 6 Replies
View Related
Sep 24, 2013
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
View 2 Replies
View Related
Mar 24, 2014
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
View 2 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Apr 26, 2013
calculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%
View 3 Replies
View Related
Jan 19, 2009
I'm trying to create a formula in 1 conditional format rule. The goal is to check if al cel contains "1" or "Z". If the value is "1" the cell must turn black and if it is "Z" it turns red.
If this is not possible, I'd like to only turn it black.
I am not able to use vba and have only 1 conditional formatting rule left in my excel 2003 sheet.
View 9 Replies
View Related
