Conditional Format - Error In Icon Set For Percentage?

Dec 13, 2012

I've been using conditional formatting for some times now, but this is the first time I have this problem..

I've using conditional formatting to display icon besides a sets of numbers/percentage. The condition is as follows:

--- >= 75, green
--- >= 60, yellow
--- < 60, red

When I used this for "number", it works flawlessly.

But when it comes to "percentage", disaster strikes.. I am getting a Yellow for 75%, and red for 60%.

Below is a screen shot that I did to illustrate the issues. The set of numbers on the left is OK. But the same set on the right, just in percentage, doesn't work well..

View 2 Replies


ADVERTISEMENT

Conditional Format The List So That It Shows The Lowest Percentage In 'green' And The Highest In 'red'

May 13, 2009

I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

View 5 Replies View Related

Conditional Formatting - Icon Sets According To Value % Does Not Execute?

May 9, 2014

I'm trying to insert a simple conditional formatting rule - icon sets according to rule percentages I've selected (Green > 90%; Yellow < 90% but > 80%; < 80% red). The conditional formatting does not work once i select okay. For example. The data set shows 95% as red, 83% as red, it's just not working for me. I've tried opening a new workbook, new worksheet and nothing seems to work. I've been able to do insert this rule based on "number" setting but unsuccessful with percentages.

View 2 Replies View Related

Trend Up / Down Arrows Using Conditional Formatting Icon Sets

May 30, 2014

I am looking to show Month Wise Trend Up/Down/Nuetral Arrows for the Achievement % for each month.

I want to show the Increase or Decrease trend of the % in the next month.

I have Baseline figures (count) and i have count of Mar, Apr, May months. (Column A)

I have calculated the Achievement % by 100%-(Actual/Target) (Column D, G, J for Mar, Apr, May Months)

I want to show the Increase or Decrease Trend Up/Down/Nuetral buttons either by the Achievement % or by the Total Incidents Each month compared to the Baseline Figures (Column A).

I tried to use Icons Set in Conditional Formatting but i was not able to do that.

Sample file attached : Sample.xlsx

View 2 Replies View Related

Conditional Formatting - Change ICON SETS Color

May 23, 2014

Is it possible to change color for arrows icons in CF- icon sets? I would really like to have a five arrows pointing in different directions (all in different colors).

Can I do it in conditional formatting or should I go for "Wingdings" fonts and then change font color?

View 2 Replies View Related

Excel 2007 :: Custom Icon Sets For Conditional Formatting?

Mar 26, 2010

Any way to import and use icon sets for conditional formatting other than the ones provided in excel 2007? I would like to have some circles and shapes in colors other than just yellow, green, red, and gray.

View 5 Replies View Related

Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format

Jun 7, 2006

I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.

View 7 Replies View Related

Excel 2010 :: Icon Conditional Formatting Not Working When Formula Present In Active Cell?

Mar 20, 2014

I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.

View 3 Replies View Related

Conditional Formatting Based On Percentage And Insert Cell Based On Percentage

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

Email Icon In Spreadsheet As Per Email Icon In Quick Access Toolbar

Aug 20, 2014

I need to send out an order form (spreadsheet) to 100's of people that need to complete the form and email back to me as an attachment. If I was completing the order form myself I would use the "email" icon that I have pinned to my Quick Access Toolbar (QAT). However, most of the recipients don't even know the Toolbar exists.

Is there a way I can insert an icon / hyperlink in the spreadsheet that does the same thing as the QAT icon. I can insert text to say "click here to email your order" (or similar).

I need to keep it in an excel format and an icon is so much better that asking them to save to their hard-drive and attach to an email, etc.

The QAT icon is exactly what is needed but I need to provide a spreadsheet that works for folk who haven't got the icon.

View 4 Replies View Related

Conditional Percentage Based On Conditional Sum

Mar 13, 2008

I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g

1400 156
1400 99
1400 74
1400 86
1400 256

The number of entries may vary for each block but is never more than 60. the next block may be e.g.

14:05 15
14:05 42
etc

and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............

View 5 Replies View Related

Format A Percentage With Fraction

May 13, 2009

Is there any way of formatting a cell to show a percentage with a fraction? For example, I'd like to show: 3 1/3%, 1 2/3%, 2 1/2%, etc.
I'm showing data to a client who will pull out his calculator and add up the decimals on his calculator and be unhappy that the numbers don't add up to 100%.

I can force the output by using TEXT() or multiple columns, but ideally, I'd like a custom format if possible.

View 9 Replies View Related

Custom Number Format - Percentage

Jul 8, 2014

I am trying to create a custom number format.

Here is what I have come up with: 0.00%;[Red]-0.00%

The problem is, it has no "," it would display as 23555555.6% or -2355555.6%

I need it to display as: 23,555,555.6% or -23,555,555.6%

How would this be written?

Additionally, I have occasion to "abbreviate" in the following format:

23.55MM% or 1.5M% or -23.55MM% or -1.5M%

For currency, I have it written in this manner:

Currency: [>=1000000] $#,##0.0,," MM";[>0] $#,##0.0," M";[Red]($#,###," M")

I just freeze when trying to convert to percentage.

View 1 Replies View Related

Conditional Formatting Based On Percentage

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

Conditional Formatting For Percentage Values

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

UserForm - Format Textbox For Percentage Vs Decimal?

Dec 20, 2012

I have the following code

Code:
Private Sub ComboBox1_Change()
TextBox1.Text = Range("P" & UserForm1.ComboBox1.ListIndex + 3).Value
TextBox2.Text = Range("N" & UserForm1.ComboBox1.ListIndex + 3).Value[code].....

In TextBox3, the user will input a percentage as a whole number, IE: 5 for 5%, but when the update is made, it is writing the "5" as 500% and is writing 50 as 5000%.

If there is already a percent in the cell it calls up IE: 50%, it shows up in TextBox3 as .5%

View 1 Replies View Related

Format All Percentage Cells To X Decimal Places

May 17, 2008

I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)

View 2 Replies View Related

Conditional Formatting Based On A Percentage Difference

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

Conditional Budget Difference And Percentage Formulas

Mar 16, 2014

In looking for examples of business budgets I found a sample spreadsheet that used these two conditional statements:

A) to calculate the difference between the Budget and Actual numbers: =IF(OR(B10,A1), B1-A1,) where B1 is Actual $ and A1 is the budget $.

B) to calculate the percentage (+ or -) =IF(A1,C1/A1,) where A1 is, again the budget, and C1 is the result of the difference calculated in A) above.

What is the advantage of these conditional formulas over the more straight forward B1-A1 and C1/A1

View 1 Replies View Related

Overflow Error 6 Calculating Percentage Change

Jul 2, 2012

I was quite a few problems using worksheetfunction.ln() to calculate % change. I decided to write more basic code to try to accomplish this but I'm getting an overflow error.

Code:
DRows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))
For r = 1 To (DRows - 1) '***** -1 is bc of ln property
percentchange = 0

newval1 = Worksheets("INFO").Cells(1 + r, 2).Value
old1 = Worksheets("INFO").Cells(1 + r, 2).Value

percentchange = (newval1 - old1) / old1
Worksheets("info").Cells(2 + r, 3).Value = percentchange
Next r

View 6 Replies View Related

Save Conditional Format Colour But Delete Condition Format

Dec 19, 2006

I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.

I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.

Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.

View 9 Replies View Related

Conditional Formatting Percentage Axis For Thermometer Chart?

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

Conditional Formatting That Will Adjust Based On Percentage Ranges

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

Conditional Format Based On A Conditional Format

Feb 9, 2010

I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.

Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.

View 2 Replies View Related

Copy And Paste Actual Format From Conditional Format?

Apr 12, 2012

I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.

View 5 Replies View Related

Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.

Possible???

View 3 Replies View Related

Conditional Formatting - Colouring Percentage Of Multiple Cells Based On Date Range

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

Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View 2 Replies View Related

Conditional Format Or Another Way?

Apr 22, 2014

how to view changes on my spreadsheet.

I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....

View 2 Replies View Related

Conditional Format According To Day

Dec 16, 2008

I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.

I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved