Is there any way to make a cell display both a raw number and a parenthetical representing a percentage based on a formula? I know this may sound strange.
Examples:
9 (25%)
12 (39%)
I could get it done via merging or =_&_ style entries, I know, but is there any other way?
I know for some of you this will be pretty simple but im having trouble figuring it out. Attached is a shortened version of what i am trying to do. I want the Percent Attendance column to represent the cumulative percentage(hope I used the correct phrase). So for Person 1, it should currently say 100%, person 2 it should say 66.67% and so on. There are 5 days that i want to get the percentage, but because we haven't gotten to 2 of them yet, using a regular sum formula for the entire five days gives me the wrong values.
View 2 Replies View RelatedI 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.
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?
Is there a way to add a percentage to a number, I know the Method of Multiply by 1.25 for 25% The problem is there another way to formulate without the "1."
Sheet1 LMN
32640%15.28?
I have a spreadsheet with 6 sheets. There are 3 sheets that have information regarding the soil, geology and drift deposits of an area. The other 3 sheets relate to these sheets to display the information for each area. The way that the spreadhseet is set out pretty much explains what i am trying to archive. Basically I want each sheet to say whether or not the rock, soil or drift type is present for each area. And if it is present to calculate the percentage of total area. I have created a pivot table for each of the info spreadsheets - but need it to look like the "table" sheets, because it needs to be added to a report. Any questions and i'll try and answer them , but it's quite difficult to explain in words what i am trying to achive.
View 4 Replies View Relatedexample 1:
This years sales are $3700, a decrease of 11.6%. What would last years
sales be?
example 2:
This years sales are $4500, an increase of 151%. What would last years
sales be?
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out.
Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
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.
I would like to use vba to copy a value in F31 to B5 in sheets("2") and express it as a percentage.
For example, the value in F31, 102 is copied to B5 as 102. Like to have it express as 102%.
Sheets("S1").Select
Range("F31").Select
Selection.Copy
Windows("Report.xls").Activate
Sheets("2").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
"in Excel" - my formula for cell C11 is: "What percentage of the time (occurrences) was the value in cell B11 greater than the values in A1 thru A10?"
As in...:
100
99
98
97
96
95
94
93
92
91
90...95.....40%
89...16.......0%
88...123...100%
87...90......20%
So no data in first 10 rows of Col B. (Or Col C, of course.) Then for the value that appears in B11, how many times (out of 10, in this example for sake of ease) was that value greater than the entries in the first ten entries in Col A:
95 (cell B11) was greater than A10, A9, A8 and A7 (91, 92, 93 and 94). So 4 times out of 10, B11 was greater; 40% is the answer the formula would yield.
16 was never greater than any entries in A2 thru A11, so 0%.
And 123 was greater than all the entries in A3 thru A12, so 100%. Etc, copied down the sheet...
Spreedsheet on on sales and taxes
Need formula for cells.
3 different cells involved... B1 will have $ amounts, B10 tax code, B25 will have total of tax applied due to code.
Exp; cell B10 will contain the tax code... if you enter..... "1" in the cell the calculation will be 7% in cell B25, enter "2" in cell B10 and the calculation will be 6% in cell B25 , and if you enter "3" in cell B10 the calculation will be 13% in cell B25.
On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:
Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)
The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%
Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%
Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%
Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%
I have data one sheet which is
Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100
On the next tab I am consolidating all the reference numbers and want to capture the latest % complete
I have been trying =Max(if ref no=1 and type = Erect, % complete),1
But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.
I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.
A = Departments
B = Calls Dropped
C = Calls Received
D = Current % (C/F)
E = Target %
F = Total (B + C)
G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)
Departments
Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1
21083.33%95.00%12 Dept 2
067100.00%90.00%67 Total
27791.67%95.00%79
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
I have a dataset in Excel 2007 where I need to determine the number of census tracts (as rows) it takes for each column of data values (populations of different ethnic groups) to reach a cumulative percentage of 50% of their City group total. This is a measure of concentration that will vary from group to group.
I can do this manually by sorting each column from largest population percentage to smallest population percentage, then shift-click selecting the first row, scrolling down until I see the sum of percentages shown in the bottom right corner first reaches 50.000% or more.
Is there a cell formula I can use to determine the number of rows it takes to meet the criteria? I have to do this for 250 columns.
Is there a cell formula I can use to determine the number of rows it takes for a column of data values to reach a specific amount, that is first reach a cumulative percentage of 50.000% of the column total? It must be based on the data values from largest to smallest but I don't want to have to manually sort each column I have.
If a cell is not blank, then increase that amount by a percentage identified in another cell and display the value only without any formalas in a new cell. To this end I wrote this IF statement:
=IF(I2>0,CP9=(CA9*I2/100)+CA9,CP9="")
Observation: I see a FALSE in CS9 where this IF statement exists, however, CP9 does not display the required value.
I'm trying to create a formula that takes from "Manual" Sheet the number from colume G2 and multiply it by the percentage in sheet "AllocationRule".
My formula currently is =Manual!$G$2*AllocationRule!$B5.
What needs to happen is that the total number in "Manual" needs to be distributed evenly in 4 rows by the percentages allocated in "AllocationRule".
Right now I can't copy my formula over to the sheet because the "AllocationRule" should stop at B5 and not go further and the G2 from "Manual" should not change for the percentage allocation but should change to the next row for the next month.
And then after I've done the calculation I want the LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.
The cell I am referencing value is 100,000
I need to take two percentages off of that number. First 50%, then 37% and have the value in a SINGLE cell.
Because 100,000/1.87 is not the same thing....of course.
I need a formula that can work out a percentage increase or decrease from cell J2 to L2.
J2 is the current data with L2 being the previous data - so the formula would compare J2 with L2 and tell me if there is an increase or decrease shown as a percentage.
The figures fluctuate from month to month so they could be either an increase or decrease. There are over 300 entries so I can't go through each one adjusting the formula accordingly - this is why I need the formula to do the working out if it is up or down.
I would like to display the zero percent (0.00%) value in a single cell on a worksheet. Typically, there will be a positive value input in the cell such as 3.50% but occasionally it will require a zero value. When I type the zero value, the cell is displays a blank. I do not want to show zero values on the remainder of the worksheet.
View 2 Replies View Relatedhere is the formula I am using. =IF(A5 < =Schedule!$K$9,100%,0%) We only track complete and not complete....so my formula above just comes out to 100% or 0%. What I am wandering is if I can add a modifier so that it can be adjusted w/o just over writing the formula. Ok so my vision is, the formula above is in cell B5.....next to it in cell C5 I would like a modifier so that if that amount should really be 85%. I can type 85% in C5 and it will change B5 to 85%. So I guess the formula would be more like =IF((A5 < =Schedule!$K$9,100%,0%),or = C5))
View 2 Replies View RelatedI have various textboxes and they are entering values onto my sheet on my command.
If i say, enter 10 into a textbox, meaning 10%, on my spreadsheet it comes out as 1000% I thought about being clever and putting
range("a1") = textbox1.value / 100
but this brings up an error with the debugger!
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 RelatedGood 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?
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
I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 1-10, are assigned by a % bracket:
1-20% = 1
21-40% = 2
41-60% = 3
61-80% = 4
81-100% = 5
101-120% = 6
121-140% = 7
141-160% = 8
161-180% = 9
181-200% = 10
There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
View 6 Replies View RelatedMy 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.