I have imported ROI data for securities to Excel. However, when the ROI
comes over, it is formatted as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and I get 130%.

To calculate the sum of a range of cells in Excel, you can use the SUM function. Here's an example:

This formula will add up the values in cells A1 through A10 and display the result in the cell where the formula is entered.

If you want to calculate the average of a range of cells, you can use the AVERAGE function. Here's an example:

This formula will calculate the average of the values in cells B1 through B5 and display the result in the cell where the formula is entered.

If you need to perform more complex calculations or automate tasks in Excel, you can use macros or Visual Basic code. Here's an example of a simple macro that sorts a range of cells in ascending order:

To use this macro, open the Visual Basic Editor in Excel by pressing "Alt + F11" on your keyboard, then insert a new module and paste the code above into it. You can then run the macro by pressing "F5" or clicking the "Run" button in the toolbar.

"Fdn Girl" wrote:
I have imported ROI data for securities to Excel.
However, when the ROI comes over, it is formatted
as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and
I get 130%.

You could use the Custom format `0.0\%` without quotes.

But I think that is a poor idea. Note that if you ever use that cell
reference in an arithmetic expression, you must remember to divide by 100
each time, e.g. =A1*A2/100, where A2 contains the number 1.3 formatted as
`0.0\%`.

I think it would be better to convert the imported number. You can do this
by putting 100 into an unused cell and copy it. Then select the ROI cells,
right-click, and click Paste Special Divide OK, and format the ROI cells
as Percentage. You can delete the 100.

1.3 equals 130%, it's not a matter of formatting. You need to divide those
values by 100, then format as %.

To do that, type 100 in a cell somewhere, then highlight it and go Copy,
then highlight all the 1.3 etc cells, right click, select Paste Special, and
check Divide.

Now format them % (change decimal place to 1) and there you go.

"Fdn Girl" wrote:

I have imported ROI data for securities to Excel. However, when the ROI
comes over, it is formatted as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and I get 130%.

1.3 is 130%. The same way 0.5 is 50%. You are best off to divide your numbers
by 100 and formatting those. You could do a custom format but to use the
value in a calculation you will have to divide it by 100 anyway.
--
HTH...

Jim Thomlinson


"Fdn Girl" wrote:

I have imported ROI data for securities to Excel. However, when the ROI
comes over, it is formatted as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and I get 130%.

I wrote:
I think it would be better to convert the imported number.
You can do this by putting 100 into an unused cell and
copy it. Then select the ROI cells, right-click, and click
Paste Special Divide OK, and format the ROI cells
as Percentage. You can delete the 100.

If that seems too tedious to do every time you import data, we could show
you how to put the operations into a macro and even create a "button" to
execute the macro each time.


----- original message -----

"Joe User" wrote:
"Fdn Girl" wrote:
I have imported ROI data for securities to Excel.
However, when the ROI comes over, it is formatted
as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and
I get 130%.

You could use the Custom format `0.0\%` without quotes.

But I think that is a poor idea. Note that if you ever use that cell
reference in an arithmetic expression, you must remember to divide by 100
each time, e.g. =A1*A2/100, where A2 contains the number 1.3 formatted as
`0.0\%`.

I think it would be better to convert the imported number. You can do this
by putting 100 into an unused cell and copy it. Then select the ROI cells,
right-click, and click Paste Special Divide OK, and format the ROI cells
as Percentage. You can delete the 100.

You could use a macro to divide all by 100 before formatting.

Sub NumToPercent()
For Each c In Selection
a = c.Value
If IsNumeric(a) Then
If a < 0 Then a = a / 100
c.Value = a
c.NumberFormat = "0.0%"
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Wed, 14 Apr 2010 12:37:02 -0700, Fdn Girl <Fdn Girl
@discussions.microsoft.com wrote:

I have imported ROI data for securities to Excel. However, when the ROI
comes over, it is formatted as 1.3, instead of 1.3%. When I try to change to
format to %, it automatically multiplies by 100 and I get 130%.