Displaying Percentage Values Over 100%
Feb 4, 2008
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?
View 9 Replies
ADVERTISEMENT
Jun 25, 2012
My Excel 2007 worksheet contains a cell where a percentage is manually input. A freight cost is calculated based on the input percentage. Typically, the percentage is 3-7% but once in a while freight is excluded and the percentage is zero.
My issue is that when a 0 is input the cell appears blank and I would like it to display 0.00%. The remainder of the worksheet needs to have the zero display turned off.
View 3 Replies
View Related
May 31, 2012
I have two columns in my spreadsheet, and both are dates. Using a calculation (one date minus the other) I get the duration (in days) between the two.
I also apply color coding to say if the duration is > X, then color code as red, etc.
All the color coding is working fine for values that are either > or < 0, but anything where the two dates are the same (date 1 - date 2 = 0), the value shows as blank.
When I click on the cell, the value "0" is actually IN the cell, but it's now showing, as if the font was colored white or something, but it hasn't.
I don't have anything in the code telling the worksheet not to display "0" values, so I don't know where to go...
View 5 Replies
View Related
Mar 30, 2009
I have some values in the column and i want those values to be displayed in rows now. Example:
In the Excel sheet the data is in the form,
Column1 Column2 Column 3
TASK_TYPE NE_TYPE ADC_PARAM_ORL
I want in the format,
Column1
TASK_TYPE
NE_TYPE
ADC_PARAM_ORL
View 2 Replies
View Related
Jun 26, 2008
Is there a formula/function in excel that can take all the cells in a column, lets say Column A, and paste/display it in another file without the spaces between the cells with values and without duplicates?
Worksheet1
Column A
1 Name
2
3 Mike
4 Rob
5 Ryan
6
7 Mindy
8 Paul
9
10 Rob
11
12 Mindy
13 Chris
Worksheet2
Column A
1 Name
2 Mike
3 Rob
4 Ryan
5 Mindy
6 Paul
7 Chris
8
9
10
11
12
13
View 11 Replies
View Related
Mar 13, 2009
is to display a set of data based on filtered information.
My Data base:
Company Department Name
A X John
A Y Joe
A X Jane
B Y Bob
C Z Kate
A X Kerri
Based on user selection of Company and Department, I want to be able to display the relavent names.
If user chose Company A, and Department X, I want to be able to display
John
Jane
Kerri
I've used the ROWS, Index, Small combination that works perfectly (Please see sample below). However, since the this software doesn't support the ROWS function, and doesn't support Macros.
View 7 Replies
View Related
Nov 15, 2008
I have a spreadhseet where columns I and J (range from I6 to J300) serve as input cells, off to the right, 23 columns over in AF and AG respectively I have a hidden array formula (Index, match) calculating values based on input in either column I or J and several factors embedded in reference table in the same sheet. That works fine. I want cells in columns I and J to be interdependent, in other words, input in column I drives calculations in a hidden formula and I want the value of that calculaton to display in column J (in a adjacent cell input in I6 results in display in J6), but if I input value in J then this value will drive calculation in a hidden formula and display in I (let's say I is centimeters and J is inches). I have a code that works (I set it up as a try just for few rows) but only one code section at a time, not together. If I choose column I (#9) to go first in code, values update in J, but not the other way around, if I choose column J (#10) to go first in code, values update in I, but not the other way around. What am I doing wrong, I tried Target.address case, I tried Intersect ... is nothing then etc. They all work one at a time but not together. Here is the code as it stands now
View 3 Replies
View Related
May 31, 2012
I'm using Windows XP with MS Excel 2003. I have a pivot table representing a survey. Let's say I've built the survey outside of excel and I've imported the response data into Excel. One of the questions in the survey is "ratings" and the possible valid responses for it is: "Excellent", "Good", or "Poor". In my data set in excel let's say I have 10 responses or rows and all the responses for the question on ratings are either "Excellent" or "Good". (There are no rows with a "Poor" value in the ratings column).
For example, let's say out of the 10 responses, 6 are "Excellent" and 4 are "Good". As such my Pivot chart shows two bars: one for the number of respones with "Excellent" (10) and another bar for the number of responses with "Good" (4). My delima is how to show a third bar showing "Poor" with a zero as the number of responses.
View 2 Replies
View Related
Oct 7, 2008
I need to coloring percentage values in excel 2000. What I would like to do is have percentages greater than zero shown in green, while negative values are red, and zero values are black. I currently have the custom formatting so negative values are red and all others are black, but I cant' seem to find a way to change it so greater than zero numbers are green and zeros are black. All I can do is chang positive and zero at the same time. Here is what I currently have:
View 3 Replies
View Related
Sep 15, 2009
I am trying to create a formula that can work out the % difference between two values. The formula I am using is:
=(B1-A1)/ABS(A1)
So, where
A1=1, B1=2, = 100%
A1=-1, B1=2, = 300%
This seems to work perfectly, apart from where there are zero numbers involved. The answers I want to have are as follows:
A1=0, B1=0, = 0%
A1=0, B1=-1, = -100%
A1=0, B1=1, = 100%
A1=-1, B1=0, = 100%
A1=1, B1=0, = -100%
View 3 Replies
View Related
Jun 19, 2009
I am trying to exclude a certain value from an array of numbers to get a percentage. I have data that populates on its own and in the same columns regularly. The data contains a country name in column A, a year in columnB and a value in column C. I am trying to exclude the value for the U.S. in the totals so that I can get a percentage of values outside of the U.S.
So that the sum of all of the rows less the value corresponding to the U.S., divided by the sum of all of the values.
The problem is that the location of the U.S. value and U.S. designation varies with different inputs (this cannot be helped), so the formula must be able to follow it.
I have been able to do this but not without a lot of extra cells and rows, and I would like to have a single formula.
View 5 Replies
View Related
Jan 16, 2008
How do I change numerous numbers in several different columns by a constant amount? example reduce each number in each of these columns by 50%.
View 9 Replies
View Related
Dec 2, 2013
I'm designing a spreadsheet currently. I want to input a number into cell A1 - for example 13. Now the number 13 has a percentage value of 67.3%. When I enter the value into A1 I want excel to automatically fill cell A5 with the percentage value of 67.3%.
I need to have the numbers from 1-20 all with varying percentage values.
1 = 100%
2 = 97.8%
3 = 94.3% etc..
So essentially I would like to enter 13 into cell A1 and for cell A5 to automatically fill in the value of 67.3%, if I was to change the value in A1 to 10 it would also automatically change the percentage from 67.3% to 74.7%..
View 10 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
Jun 9, 2009
What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.
What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....
View 6 Replies
View Related
Feb 13, 2009
I have a little table:
Columns A:B (separated by commas)
Row 1- Year One, 20
Row 2- Year One, 20
Row 3- Year One, 10
Row 4- Year One, 30
Row 5- Year One, 20
Row 6- Year One, 20
Row 7- Year Two, 10
Row 8- Year Two, 20
Row 9- Year Two, 20
Row 10- Year Two, 10
Row 11- Year Two, 20
Row 12- Year Two, 40
In the adjacent cells in columns C (C1:C12) I need a formula that would turn the value into a percentage of the total of the values for that year (e.g. in C1: 20 is 16.7% of 120, so the value displayed would be 16.7% (or 0.167)).
View 3 Replies
View Related
Jan 16, 2014
I have a list of group ID codes, which contain 1 or more product codes within them.
Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below
Group ID
Product Code
Alternative
56381
240027
160380
[Code] .....
Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.
So for the example above, group ID 56381 would have 50% codes with alternatives.
By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)
However some groups contain over 100 codes and the spreadsheet is 40K rows!
View 5 Replies
View Related
Dec 20, 2011
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
View 3 Replies
View Related
May 23, 2013
I am trying to add 2 cell values together then show the total as a % of a value in a 3rd cell, however I also need it to allow for 0 values in the chosen cells without displaying an error message or it messes up the average formula elsewhere on the sheet?
View 2 Replies
View Related
Jan 8, 2009
Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.
33%
33%
33%
NA
25%
25%
25%
25%
NA
100%
NA
20%
20%
20%
20%
20%
NA
Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?
View 11 Replies
View Related
Jan 27, 2010
How would I write a formula that takes cell values from six worksheets and adds them up as a percentage (quarterly updates)? I realize this is wrong, but here's what I'd tried (for three sheets).
=+SUM(IF(EVH!F7="Y",25,0)+IF(FUN!F7="Y",25,0)+IF(HES!F7="Y",25,0))/3
View 9 Replies
View Related
Aug 24, 2014
Data is;
83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.
So, my bullseye percentage = 43209/83300, or ~51.87%.
Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.
Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.
View 5 Replies
View Related
Dec 18, 2013
I have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?
View 1 Replies
View Related
Apr 5, 2014
I have a daily report that gives a percentage based on what is typed into the "Supplier" and "Total Sales" columns but I'm having trouble writing the following formulas since my excel knowledge is relatively low.
1. Where a cell in the "Supplier" column says anything but "Den", "Burrowed" or "Studio Nyx" I need the "60%" and "40%" columns filled with the relevant formula (=B11*60% for example) and the "100%" column blank.
2. Additionally, if the cell contains "Den", "Burrowed" or "Studio Nyx" I need the "100%" column filled and the "60%" and "40%" blank.
View 6 Replies
View Related
Oct 25, 2005
I have a single column of numerical values that may repeat many times within
the column.
I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered – so only take into account Visible Filtered Cells.
The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.
The Formula should Return two numeric values: a Start Value and an End Value
– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.
The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.
View 9 Replies
View Related
Sep 10, 2012
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
View 1 Replies
View Related
Apr 21, 2007
Is It Possible to use VBA Coding to Display An Image (Object),
And Un-Display or Remove that Same Image (Object) using a VBA Code?
Example:
I have a Mailbox Picture that I would Like to Show, but only when a cell's
value = 5. If the Cell's Value is not 5, then don't display the mailbox.
View 12 Replies
View Related
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
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
Nov 24, 2009
I have tried different formula involving with decimal places.
If A1 has a whole number like 5141234 then the result would come out as a whole number =(A1-5148000)*1.000440935+48000
If A1 has three decimal places like 5142356.654 then the result would out as three decimal places
View 4 Replies
View Related