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
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
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
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
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
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 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
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
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
View Related
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
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
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
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
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
Mar 14, 2009
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:
For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.
What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.
Therefore, I came to the conclusion that:
0.25% x 80 = 20 hours
0.50% x 80 = 40 hours
0.75% x 80 = 60 hours
Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
View 9 Replies
View Related
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
Apr 23, 2007
I want to be able to change the font color of the numbers in a column based on even or odd. I selected the columns and formated them to all be blue... thats the color I want for even, but now I can't figure out how to set a condition that checks for odd numbers and changes them to red. I now have a count of even/odd for all records, and even/odd for the last 30, this provided some very interesting results.
View 9 Replies
View Related
Jan 18, 2008
I have 64 squares that will produce some repeat letter sequences. How do I make it so that When there are repeats (multiple instances of the same result) they turn the cell blue or whatever. I would like to specify again that there will be may more than one repeated sequence so its not as easy as I thought it would be so like the 1st set could be clue and the next set would be green or whatever.
View 14 Replies
View Related
Feb 2, 2012
I currently have a spreedsheet which has a conditional formatting cell where if the figure is above 1 is green and below red what i am trying to do is get a sum to calculate based in its color?
I.E. I have a value in cells F7+F25+F27+F29 i then have a value in F17 but only want this to be included in the total calcualtion if the figure in E15 is equal/above the value of 1.
View 3 Replies
View Related
Mar 11, 2008
what would I do if I want cell B:1 to be formatted a certain way if the value in A:1 is a certain value?
View 9 Replies
View Related
May 8, 2008
I have a little problem I would like some assistance on. I pull data from a data warehouse into an Excel spreadsheet that is linked to another spreadsheet that consolidates the data. I need to add the PGL Grads only for completed classes which are usually show zero (Actual Grads column) unless there were no entries (Actual Entries). Currently, the formula used is =SUMIF($I$104:$I$118,">0",$H$104:$H$118) but this can cause problems if there is a zero value for a completed class. In the example below, the total for PGL grads is 142 but in reality it should be 158, the zero value for class 2008008 skewing the result. I have tried variations of SUMIF, SUMPRODUCT, and SUM and end up with the same results. The warehouse data puts zero values in the Actual Grads column for classes that have not completed and I know I can make this work with blank values but that just creates another step in the process ...
View 9 Replies
View Related
Sep 11, 2008
I have to color direct payoffs and commissions (green and blue respectively). I have to have a running total of payoffs that have not cleared and one for commissions. I have set up conditional formatting to color the text based on the check classification (A for payoffs and C for commissions). What I am trying to do is write a formula that will count only the payoffs (green text) that do not have a cleared date. I am then trying to do the same for the commissions (blue text). these totals are needed separately so I do not need them combined.
I have tried sumif and sumproduct. I have since deleted the formulas i have written cause they did not work.
View 9 Replies
View Related
Jul 29, 2014
In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.
View 8 Replies
View Related
Aug 18, 2014
On the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies
View Related
Mar 30, 2014
I have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
View 2 Replies
View Related
Apr 9, 2014
I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.
View 13 Replies
View Related
May 30, 2007
i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.
when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.
the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database
what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.
View 13 Replies
View Related
Nov 6, 2008
I have a row of cells pertaining to jobs B3 across to I3.
In B3, C3, D3, E3 the value entered is text.
In F3, G3, H3, I3 the value entered is time.
What I am trying to do is to change the Background colour to Red on cells
F3, G3, H3, I3 if either H3 or I3 or are empty at the end of the current day but only if there is some text in any or all of the cells B3 - E3
So for example if H3 or I3 have no time put in them by 23:59 today, when the workbook is opened tomorrow the cells background colour will turn red.
Not sure if this if poossible or not? hopefully I have explained it well enough but if not pease let me know.
View 14 Replies
View Related
May 4, 2009
I have a field where the month is written as text. I would like to highlight row whose month column has passed. My conditional formating code is:
View 4 Replies
View Related
Feb 17, 2010
I want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to $20 the entire row would be highlighted with a color.
the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.
View 12 Replies
View Related
