Decimal Number To Percentage Formula (newbie Question) 


Decimal Number To Percentage Formula (newbie Question)  Excel 
View Answers 
I've got a column full of numbers like 0.5, 1.0, etc. I need them to look
like 0.500%. I figured out how to format the cells so that 0.5 looks like
0.500, but when I change the cell format to percentage, it makes 0.500 into
50.000%.
So I figured out how to make a formula that divides 0.500 by 100
(=R1C6/100), resulting in 0.005, which I can format to a percentage and get
0.500%.
Where I'm stumped is in applying that formula to the entire column. Instead
of =R1C6/100, I want my formula to say =all rowsC6/100. How do I tell it
{allrows}?
Thanks for putting up with a newbie
Ken Benson
like 0.500%. I figured out how to format the cells so that 0.5 looks like
0.500, but when I change the cell format to percentage, it makes 0.500 into
50.000%.
So I figured out how to make a formula that divides 0.500 by 100
(=R1C6/100), resulting in 0.005, which I can format to a percentage and get
0.500%.
Where I'm stumped is in applying that formula to the entire column. Instead
of =R1C6/100, I want my formula to say =all rowsC6/100. How do I tell it
{allrows}?
Thanks for putting up with a newbie
Ken Benson
Similar Excel Tutorials
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Convert Numbers Stored as Text to Numbers in Excel
I'll show you 4 ways to convert numbers stored as text to numbers in Excel. This situation often happens when you ...
I'll show you 4 ways to convert numbers stored as text to numbers in Excel. This situation often happens when you ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Format Any Element of a Chart in Excel
In this tutorial I am going to go through the Format tab in more detail and show you how to Format every element of ...
In this tutorial I am going to go through the Format tab in more detail and show you how to Format every element of ...
Helpful Excel Macros
Format Cells as a Percentage in Excel Number Formatting
 This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
 This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Format Cells in The Number (Numerical) Number Format in Excel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Filter Data to Show the Bottom X Percent of the Data Set in Excel  AutoFilter
 This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
 This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Similar Topics
Hello,
I've got some numbers on my excel spreadsheet and a cell to calculate the percentage.
For example, I've got cells called:
(C2) Target = 47
(C3) Achieved so far = 46
(C4) Percentage = 97.87%
I've formatted the percentage cell to 2 decimal places.
However, later on in the spreadsheet, I have a formula in C10 saying:
="Percentage of work achieved so far is " & C4
The cell returns "Percentage of work achieved so far is 0.978723404255319
How do I get it to just say 97.87%? Can I format the number within the formula? Or how can I get the string formula in C10 to return just the displayed contents from C4?
Thanks in advance!
I've got some numbers on my excel spreadsheet and a cell to calculate the percentage.
For example, I've got cells called:
(C2) Target = 47
(C3) Achieved so far = 46
(C4) Percentage = 97.87%
I've formatted the percentage cell to 2 decimal places.
However, later on in the spreadsheet, I have a formula in C10 saying:
="Percentage of work achieved so far is " & C4
The cell returns "Percentage of work achieved so far is 0.978723404255319
How do I get it to just say 97.87%? Can I format the number within the formula? Or how can I get the string formula in C10 to return just the displayed contents from C4?
Thanks in advance!
I am having a problem with validating a decimal point for my formated percentage cells.
When I have a regular number format, I can limit the decimal to 4 places (0.0001). When I change the format of the cell to a percentage, the decimal validation no longer works.
Example: .0555555 is not allowed under a number format but 5.55555 is allowed under a percentage format.
How do I get the validation to limit a percentage to 4 decimals (example:5.55%)?
When I have a regular number format, I can limit the decimal to 4 places (0.0001). When I change the format of the cell to a percentage, the decimal validation no longer works.
Example: .0555555 is not allowed under a number format but 5.55555 is allowed under a percentage format.
How do I get the validation to limit a percentage to 4 decimals (example:5.55%)?
Hello,
I have a series of worksheets (~70) in which there columns that contain cells with what should be percentages reflecting efficiency. The problem is that the numbers were not entered in decimal format, so when I try to format the row as a percentage, the numbers are multiplied by 100, which is not what I want. All I want is to get a percentage sign to appear next to the numbers in these cells.
I have tried using a formula to convert the numbers to decimal form so that I could then format the cells as percentages, but then I have one row with percentages and another with numbers and no percentage signs, which is not ideal at all.
Does anyone know of a way that I can simply get a percentage sign to show up next to a number in a cell? Keeping in mind that I will have to do this to MANY worksheets and spreadhseets.
Thank you in advance
Hello,
In cells A1, A2 and A3, I have three values formatted in percentage format. In cell B1, I type in a formula to calculate the average of the three, and it automatically gives the result in percentage format. I want this as a general number so I change it manually. The problem is that everytime I change the cell reference in the formula of B1, Excel automatically reverts back to the percentage format.
How can I stop this from happening? Thanks.
In cells A1, A2 and A3, I have three values formatted in percentage format. In cell B1, I type in a formula to calculate the average of the three, and it automatically gives the result in percentage format. I want this as a general number so I change it manually. The problem is that everytime I change the cell reference in the formula of B1, Excel automatically reverts back to the percentage format.
How can I stop this from happening? Thanks.
I'm trying to change the decimal format from number format to percentage format. The problem is that all of the numbers are already typed in number format. For instance, I just want to change the format of 17.31 to 17.31% without having to retype in the number once the format is changed to a percentage format. Can it be done?
I have a column that calculates the averages of several numbers. The user can select on a userform, whether they want to see the resulting average as a whole number, or a decimal of one, or two places. I do this by applying a number format change on the column using VBA code. However, on another page I have a column with a formula in each cell that simply pulls the value from the original column. But it always shows it in a decimal format, regardless of the number format applied to the root column.
The formula is simple: =sheet4!A100 what can I do to get this to show the number in the format that the root column was changed to?
The formula is simple: =sheet4!A100 what can I do to get this to show the number in the format that the root column was changed to?
OK, this might be an easy question for someone other than myself. I am a beginning excel user who is having a difficult time getting my spreadsheet to do right.
I am multiplying a salary by a percentage. I have the destination column for the answer in accounting format so it will will only display 2 numbers to the right of the decimal point and put the dollar sign in etc.
However, when I go to sum all of the entries in the destination column, it gives be a figure but it is usually off by 12 cents.
I figure that it has something to do with a rounding issue.
Can anyone give me an example formula of how to multiply a column X a percentage & round off the resulting number to 2 digits to the right of the decimal point so when I go to add the column it gives me the correct number??
Thanks!!
I am multiplying a salary by a percentage. I have the destination column for the answer in accounting format so it will will only display 2 numbers to the right of the decimal point and put the dollar sign in etc.
However, when I go to sum all of the entries in the destination column, it gives be a figure but it is usually off by 12 cents.
I figure that it has something to do with a rounding issue.
Can anyone give me an example formula of how to multiply a column X a percentage & round off the resulting number to 2 digits to the right of the decimal point so when I go to add the column it gives me the correct number??
Thanks!!
Hello,
I have a really simple problem that I have not been able to figure out. I have a column that contains many rows of data that is in decimal format. I want these numbers that are in decimal format to be displayed as percentages rather than decimals, but without the percentage sign showing up, so really I just want to multiple this data by 100 but without creating an extra column of data. Is there a custom format I can use for this? I have not been able to figure this out.
Thanks for any help!
Hi,
I have a formula that computes for a percentage. What i want is to format the cells where negative percentages should appear with parenthesis i.e.
from: 1.20%
to: (1.20%)
Just like the accounting format or its like combinng the percentage and accountancy format.
thanks
I have a formula that computes for a percentage. What i want is to format the cells where negative percentages should appear with parenthesis i.e.
from: 1.20%
to: (1.20%)
Just like the accounting format or its like combinng the percentage and accountancy format.
thanks
Hello,
I am using concatenate formula but one of the cells is a percentage value, when I concatenate the values the percentage value appers in other format (decimal)... how can i make that the value in my formula appers in "percentage" format?
I am using concatenate formula but one of the cells is a percentage value, when I concatenate the values the percentage value appers in other format (decimal)... how can i make that the value in my formula appers in "percentage" format?
Dear Friends,
Best wishes!!!
In a pivot table a percentage column is added which calculates the percentage automatically. The percentage column shows the value with "%" (Ex. 54.12%). I tried different possibilities, but in vain.
How to avoid / reformat this to show only the value (i.e. I need only the value 54.12  instead of 54.12%)
Thanks in advance,
acsishere.
Best wishes!!!
In a pivot table a percentage column is added which calculates the percentage automatically. The percentage column shows the value with "%" (Ex. 54.12%). I tried different possibilities, but in vain.
How to avoid / reformat this to show only the value (i.e. I need only the value 54.12  instead of 54.12%)
Thanks in advance,
acsishere.
I am trying to add the percentage symbol to a column of numbers, but I do not want Excel to convert it to percentage format. My purpose for this is to use it on data that comes in from an excel plugin, so I can't change the format it is imported in. For example, it pulls 3.5, and I would like this to read 3.5%. Of course, when I make it a percentage, the number changes to 350%.
I know I could divide by 100, but I'd prefer something I can do at the click of a button (that doesn't involve a macro). I tried doing a custom format such as "#.#%", but even that changes the number to 350%.
This seems like it should be very easy, but I can't figure it out, even after lengthy Googling. Can anyone help?
I know I could divide by 100, but I'd prefer something I can do at the click of a button (that doesn't involve a macro). I tried doing a custom format such as "#.#%", but even that changes the number to 350%.
This seems like it should be very easy, but I can't figure it out, even after lengthy Googling. Can anyone help?
Hi,
I have a report which has given me a $ amount as a percentage, i have changed it to become a percentage however it has added two numerical places and for some reason i cant change this when i am in the format cells section.
At the moment the percentage looks like this: 500% but it should be only 5%, can anyone help?.
I have a report which has given me a $ amount as a percentage, i have changed it to become a percentage however it has added two numerical places and for some reason i cant change this when i am in the format cells section.
At the moment the percentage looks like this: 500% but it should be only 5%, can anyone help?.
I haven't done this simple work in a long time and I can't remember what I'm forgetting.
Column E shows the prices 15% lower than prices in Column C. I use conditional format to enter the equation:
Formula is: =SUM(C1*0.85)
I enter my numbers in column C and I can either only get 1 row in E to populate the percentage correctly, or I get nothing in any row in E when I enter prices into C. I'm trying to format the entire E column. I can't figure out why it's not letting me do the whole column or it doesn't show any numbers. I will be hiding the formula and empty cells are to be blank.
Column E shows the prices 15% lower than prices in Column C. I use conditional format to enter the equation:
Formula is: =SUM(C1*0.85)
I enter my numbers in column C and I can either only get 1 row in E to populate the percentage correctly, or I get nothing in any row in E when I enter prices into C. I'm trying to format the entire E column. I can't figure out why it's not letting me do the whole column or it doesn't show any numbers. I will be hiding the formula and empty cells are to be blank.
Hi all,
Here's what I'm trying to do. I want to divide 2 cells and have the resultant cell display as either a black + or red  percentage. Right now for example I'm using the formula (E5/D5) to obtain a percentage vaule in F5. Lets say E5 is 10 and D5 is 9 the percentage that is displayed in F5 is 110.00%. What I'd like to have displayed is +10%. Conversely if the value in E5 is 9 and the value in D5 is 10 it displays 90.00% and I'd like it to display 9% and be red. Is there a way I can accomplish this? Thanks in advance for your help and advice.
Monty Robison
P.S.  I'd like the percentage displayed to have 2 decimal points (i.e. +10.00%)
Here's what I'm trying to do. I want to divide 2 cells and have the resultant cell display as either a black + or red  percentage. Right now for example I'm using the formula (E5/D5) to obtain a percentage vaule in F5. Lets say E5 is 10 and D5 is 9 the percentage that is displayed in F5 is 110.00%. What I'd like to have displayed is +10%. Conversely if the value in E5 is 9 and the value in D5 is 10 it displays 90.00% and I'd like it to display 9% and be red. Is there a way I can accomplish this? Thanks in advance for your help and advice.
Monty Robison
P.S.  I'd like the percentage displayed to have 2 decimal points (i.e. +10.00%)
Hello all,
I have an unusual problem with Excel 2010. I have a file created with Excel 2003 with percentages and money values and it all looks fine in 2003. When I open it in 2010 it does not show the percentage, but shows me 0.9540 instead of 95.40%. Cell format is set to percentage and 2 decimals and in the formula bar it shows 95.40%.
I tried saving the file in 2010 format, reentering the percentage, creating custom format for the cell, all with no luck.
If I try to change the font, it displays all the cells as percentage, but when I close the font selection or when I change the font, it goes back to displaying 0.9540. Any ideas?
I have an unusual problem with Excel 2010. I have a file created with Excel 2003 with percentages and money values and it all looks fine in 2003. When I open it in 2010 it does not show the percentage, but shows me 0.9540 instead of 95.40%. Cell format is set to percentage and 2 decimals and in the formula bar it shows 95.40%.
I tried saving the file in 2010 format, reentering the percentage, creating custom format for the cell, all with no luck.
If I try to change the font, it displays all the cells as percentage, but when I close the font selection or when I change the font, it goes back to displaying 0.9540. Any ideas?
I've been working on this for hours to no avail.
I have one column with OUR COST, one column with OUR PRICE, one column with a percentage at the top (right now it's at 4%). I want to be able to show our mark up in the percentage column yet at the same time be able to change the percentage (ie from 4% to 5% and so on) and have the column change per the percentage. Then I want to ADD the percentage with OUR COST to create OUR PRICE. I hope this makes sense! Please help!!!!
I have one column with OUR COST, one column with OUR PRICE, one column with a percentage at the top (right now it's at 4%). I want to be able to show our mark up in the percentage column yet at the same time be able to change the percentage (ie from 4% to 5% and so on) and have the column change per the percentage. Then I want to ADD the percentage with OUR COST to create OUR PRICE. I hope this makes sense! Please help!!!!
Hi,
I work for a satellite communication company and I have to do a daily report on individual vessels and work out the percentage that they were connected to the satellite.
I am given the start time of when they weren't connected and the time they came back online. I then do a formula to work out how long they were down for. In the next column I have a formula to work out how long they were connected for. All of which is in time format. I then want to have another column which works out the percentage of how long they were connected for. The formula I have been using gives me an incorrect answer and I believe it is because of the date format transferring to percentage. (F2/24)*100 doesn't work.
Does anyone know how to help?
Thanks
Katie
I tried to do a custom formatting but it doesn't make sense. This is what I
want to do, the users of this spreadsheet are entering percentages into the
cells. The percentages have a range from 0.5% to 5%. If we format the
percentage to 2 places and we enter a 1 in then it comes up as 1% but if we
enter in .51% as .51 then it becomes 51%. How I would like to format the
cell is so that what ever whole or decimal number that is entered is shown
as a percentage i.e. if I type in 1 it will show 1% and if I type in .51 it
shows as .51%.
Is there any way to do this?
want to do, the users of this spreadsheet are entering percentages into the
cells. The percentages have a range from 0.5% to 5%. If we format the
percentage to 2 places and we enter a 1 in then it comes up as 1% but if we
enter in .51% as .51 then it becomes 51%. How I would like to format the
cell is so that what ever whole or decimal number that is entered is shown
as a percentage i.e. if I type in 1 it will show 1% and if I type in .51 it
shows as .51%.
Is there any way to do this?
Hi gang,
I have a spreadsheet, and it basically consists of 2 columns, one where there are titles, and one which has one of three values: Passed, Failed and N/A (it's a summary of test results).
I also have a percentage calculator, which calculates how much is left blank, by using the following formula:
Code:
(E2 to E99 is the column with the 3 outcomes).
This returns a value of "Percentage Done = 6.12244897959184%" when 6 out of the 92 columns are filled in.
What I want to know, is how to get that to say "Percentage Done = 6.12%" (i.e.) cut down the significant figures down to 3.
Preferably, I'd rather have the whole rounding bit in this one cell's formula.
Cheers,
RayJay.
I have a spreadsheet, and it basically consists of 2 columns, one where there are titles, and one which has one of three values: Passed, Failed and N/A (it's a summary of test results).
I also have a percentage calculator, which calculates how much is left blank, by using the following formula:
Code:
="Percentage Done = " & (((COUNTA(E2:E99))/((COUNTBLANK(E2:E99)) + (COUNTA(E2:E99))))*100) & "%"
(E2 to E99 is the column with the 3 outcomes).
This returns a value of "Percentage Done = 6.12244897959184%" when 6 out of the 92 columns are filled in.
What I want to know, is how to get that to say "Percentage Done = 6.12%" (i.e.) cut down the significant figures down to 3.
Preferably, I'd rather have the whole rounding bit in this one cell's formula.
Cheers,
RayJay.
I'm creating a formula to calculate sales commission based on monthly sales of specific products compared to each product's percent of total sales.
The commission earned (shown in column E), is calculated by multiplying the revenue in Column B by one of five percentages listed in Column F. The percentage selected from Column F is based on a percentage from Column A.
If the percentage in A is >10%, the percentage is F1, if 5, the percentage is F2, if 3, the percentage is F3, if 1, the percentage is F4 and if
The commission earned (shown in column E), is calculated by multiplying the revenue in Column B by one of five percentages listed in Column F. The percentage selected from Column F is based on a percentage from Column A.
If the percentage in A is >10%, the percentage is F1, if 5, the percentage is F2, if 3, the percentage is F3, if 1, the percentage is F4 and if
Hello
Can somebody help with a formula for the below?
I have the following information, and want to calculate the percentage complete based on a value in column a.
So column a contains a task number, and column I contains the percentage complete for the task:
1 20%
2 10%
1 5%
1 100%
3 50%
2 25%
4 9%
I want a formula in a different sheet to provide the total percentage complete for each task. So, where column A = 1, bring back a percentage (so adding up all percentages for every "1" task and dividing by the number of "1" tasks.
Hope that makes sense.
Thanks in advance....
Can somebody help with a formula for the below?
I have the following information, and want to calculate the percentage complete based on a value in column a.
So column a contains a task number, and column I contains the percentage complete for the task:
1 20%
2 10%
1 5%
1 100%
3 50%
2 25%
4 9%
I want a formula in a different sheet to provide the total percentage complete for each task. So, where column A = 1, bring back a percentage (so adding up all percentages for every "1" task and dividing by the number of "1" tasks.
Hope that makes sense.
Thanks in advance....
Hi all,
I just need the correct formula for a basic percentage calculation.
I need to add A1 and B1 to get total in C1, then divide C1 by A1 and multiply by 100 to get my answer (I will format the cell for percentage style).
How will this read?
Cheers,
JLB.
I just need the correct formula for a basic percentage calculation.
I need to add A1 and B1 to get total in C1, then divide C1 by A1 and multiply by 100 to get my answer (I will format the cell for percentage style).
How will this read?
Cheers,
JLB.
Hi,
I need a formula or cell format for this. that is, I have a percentage cells from A5:K5. Answer is like this "19.15%", "21.25%" etc.... I don't want to display "%" sign, but i need the same answer "19.15%", "21.25%". etc.... and i need last coloumn in percentage format with sign. How can i do it.
Thank you
Raj
I need a formula or cell format for this. that is, I have a percentage cells from A5:K5. Answer is like this "19.15%", "21.25%" etc.... I don't want to display "%" sign, but i need the same answer "19.15%", "21.25%". etc.... and i need last coloumn in percentage format with sign. How can i do it.
Thank you
Raj
I am looking at conditional number formatting depending on cells value in another area or worksheet
for example
i have one column for account , one column telling what should be the number format for the account and one column with the value for this account
I want the 3 column to use the number format depending of the value in the second column which is depending on the first column value
COL 1 COL2 COL 3 COL 3
ACCOUNT FORMAT VALUE >> AFTER FORMAT
Volume no decimal 73.90 74
Sales no decimal 100.10 100
Per Kg 2 decimal 1.35318 1.35
Margin no decimal 35.42 35
Margin % percentage 0.3538 35.38 %
Marg Kg 2 decimal 1
when the account is changed the format in col 3 should change according to col 2 (column 2 lookup in table what should be account format)
thanks
for example
i have one column for account , one column telling what should be the number format for the account and one column with the value for this account
I want the 3 column to use the number format depending of the value in the second column which is depending on the first column value
COL 1 COL2 COL 3 COL 3
ACCOUNT FORMAT VALUE >> AFTER FORMAT
Volume no decimal 73.90 74
Sales no decimal 100.10 100
Per Kg 2 decimal 1.35318 1.35
Margin no decimal 35.42 35
Margin % percentage 0.3538 35.38 %
Marg Kg 2 decimal 1
when the account is changed the format in col 3 should change according to col 2 (column 2 lookup in table what should be account format)
thanks