Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
I have a worksheet in excel where a single column which can be up to 1000
lines long contains a mixture of numbers and percentages. Depending on whether the figure in the column is a number or a percentage, I need to multiply the figure by either 1 or 100. Sounds simple, but I cant find any way to make excel recognise is it a real number, or is it a percentage. You dont seem to be able to look for the % sign, cant use ISNUMBER because both are seen as numbers. Can anyone help please, I am getting deperate |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
Percentages are fractions. 25% is 0.25, formatted as percentage.
So a test for <1 might be what you need, if numbers will not be smaller than 1 and percentages not greater than 100 -- Kind regards, Niek Otten Microsoft MVP - Excel "Allan H" <Allan wrote in message ... |I have a worksheet in excel where a single column which can be up to 1000 | lines long contains a mixture of numbers and percentages. Depending on | whether the figure in the column is a number or a percentage, I need to | multiply the figure by either 1 or 100. Sounds simple, but I cant find any | way to make excel recognise is it a real number, or is it a percentage. You | dont seem to be able to look for the % sign, cant use ISNUMBER because both | are seen as numbers. Can anyone help please, I am getting deperate |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
Niek,
Thanks for the prompt reply, however, here's the rub, the percentages can be greater than 100%. All was working fine until people and departments messed me up by overperforming by a large chunk. I have now started to go round in circles trying to get over the < tests Allan "Niek Otten" wrote: Percentages are fractions. 25% is 0.25, formatted as percentage. So a test for <1 might be what you need, if numbers will not be smaller than 1 and percentages not greater than 100 -- Kind regards, Niek Otten Microsoft MVP - Excel "Allan H" <Allan wrote in message ... |I have a worksheet in excel where a single column which can be up to 1000 | lines long contains a mixture of numbers and percentages. Depending on | whether the figure in the column is a number or a percentage, I need to | multiply the figure by either 1 or 100. Sounds simple, but I cant find any | way to make excel recognise is it a real number, or is it a percentage. You | dont seem to be able to look for the % sign, cant use ISNUMBER because both | are seen as numbers. Can anyone help please, I am getting deperate |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
You can also test for % format using =CELL(A1,"format")
If the result starts with a P it is formatted as % -- Kind regards, Niek Otten Microsoft MVP - Excel "Allan H" <Allan wrote in message ... |I have a worksheet in excel where a single column which can be up to 1000 | lines long contains a mixture of numbers and percentages. Depending on | whether the figure in the column is a number or a percentage, I need to | multiply the figure by either 1 or 100. Sounds simple, but I cant find any | way to make excel recognise is it a real number, or is it a percentage. You | dont seem to be able to look for the % sign, cant use ISNUMBER because both | are seen as numbers. Can anyone help please, I am getting deperate |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
Niek
Brilliant, I tried that, and it is actually =Cell("format",A1), but that is perfect, many thanks. "Niek Otten" wrote: You can also test for % format using =CELL(A1,"format") If the result starts with a P it is formatted as % -- Kind regards, Niek Otten Microsoft MVP - Excel "Allan H" <Allan wrote in message ... |I have a worksheet in excel where a single column which can be up to 1000 | lines long contains a mixture of numbers and percentages. Depending on | whether the figure in the column is a number or a percentage, I need to | multiply the figure by either 1 or 100. Sounds simple, but I cant find any | way to make excel recognise is it a real number, or is it a percentage. You | dont seem to be able to look for the % sign, cant use ISNUMBER because both | are seen as numbers. Can anyone help please, I am getting deperate |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
Presumably, your percentages are all fractions (i.e. less than 1,
unless you have values like 120%), and your other numbers are greater than 1, so you could use this to decide what you multiply with: =IF(A11,A1, A1*100) Copy down as required Hope this helps. Pete On Jan 14, 11:02*am, Allan H <Allan wrote: I have a worksheet in excel where a single column which can be up to 1000 lines long contains a mixture of numbers and percentages. *Depending on whether the figure in the column is a number or a percentage, I need to multiply the figure by either 1 or 100. *Sounds simple, but I cant find any way to make excel recognise is it a real number, or is it a percentage. *You dont seem to be able to look for the % sign, cant use ISNUMBER because both are seen as numbers. *Can anyone help please, I am getting deperate |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentages in Excel
Pete,
As you will see from the exchange with Niek it can be greater than a hundred %, but he has come up with a good answer that should do the job for me. Thanks for your help Allan "Pete_UK" wrote: Presumably, your percentages are all fractions (i.e. less than 1, unless you have values like 120%), and your other numbers are greater than 1, so you could use this to decide what you multiply with: =IF(A11,A1, A1*100) Copy down as required Hope this helps. Pete On Jan 14, 11:02 am, Allan H <Allan wrote: I have a worksheet in excel where a single column which can be up to 1000 lines long contains a mixture of numbers and percentages. Depending on whether the figure in the column is a number or a percentage, I need to multiply the figure by either 1 or 100. Sounds simple, but I cant find any way to make excel recognise is it a real number, or is it a percentage. You dont seem to be able to look for the % sign, cant use ISNUMBER because both are seen as numbers. Can anyone help please, I am getting deperate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expressing percentages in Excel | Excel Discussion (Misc queries) | |||
How do I get excel to add or subtract percentages? | Excel Discussion (Misc queries) | |||
Percentages in Excel | Excel Discussion (Misc queries) | |||
How do i work out percentages using excel? | Excel Discussion (Misc queries) | |||
Excel percentages greater than 99% | Excel Discussion (Misc queries) |