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

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

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

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

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

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

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