Format Question
in [General]
|
Prev: create chart from daily data and add trend lines for each week
Next: Macro to hide worksheet based upon text in the name
From: SITCFanTN on 8 Apr 2010 11:12 I feel silly asking this because I know it should be easy, but I just can't figure this out. I have an Excel Spreadsheet and the data is in General format and displays with one decimal and with the percent sign like this: 85.1% or 96.3%. I need to use this data in calculations so I need it to be a number format as 85.1 or 96.3...with no percent sign. I can I accomplish this. I have hundreds of rows of data that I need reformated in this way. Thanks for your help.
From: Dave Peterson on 8 Apr 2010 11:18 85.1% is the same number as 0.851 If you really need 85.1 in your calculation, you can multiply that cell by 100 in your calculation: =(a1*100)+b3 (for instance) If you want to convert all those 85.1% percentages to 85.1 (no longer less than 1), you can: Put 100 in an empty cell Edit copy that cell Select the range to fix Edit|paste special|check multiply Clean up that cell with 100 in it. And format the fixed range to General (if it's still formatted as a percentage). ===== If you need the percentage back for any calculation, you'll have to divide by 100: =(a1/100)+b3 (for instance) SITCFanTN wrote: > > I feel silly asking this because I know it should be easy, but I just can't > figure this out. I have an Excel Spreadsheet and the data is in General > format and displays with one decimal and with the percent sign like this: > 85.1% or 96.3%. I need to use this data in calculations so I need it to be a > number format as 85.1 or 96.3...with no percent sign. I can I accomplish > this. I have hundreds of rows of data that I need reformated in this way. > Thanks for your help. -- Dave Peterson
From: Marcelo on 8 Apr 2010 11:23 type 100 in one auxiliar cell, copy it and past special on the % column multiplying. format as general hth -- pleae click yes if it was helpfull regards from Brazil Marcelo "SITCFanTN" escreveu: > I feel silly asking this because I know it should be easy, but I just can't > figure this out. I have an Excel Spreadsheet and the data is in General > format and displays with one decimal and with the percent sign like this: > 85.1% or 96.3%. I need to use this data in calculations so I need it to be a > number format as 85.1 or 96.3...with no percent sign. I can I accomplish > this. I have hundreds of rows of data that I need reformated in this way. > Thanks for your help.
From: JLatham on 8 Apr 2010 11:28 Use the Format Painter? It's the little paint brush looking thing: in Excel 2003 and earlier, it's the icon in between the paste-from-clipboard and Undo icons in the standard icon bar. In Excel 2007 it's on the [Home] tab, in the Clipboard group; the bottom icon of the 3 at the left of the big Paste dropdown. "SITCFanTN" wrote: > I feel silly asking this because I know it should be easy, but I just can't > figure this out. I have an Excel Spreadsheet and the data is in General > format and displays with one decimal and with the percent sign like this: > 85.1% or 96.3%. I need to use this data in calculations so I need it to be a > number format as 85.1 or 96.3...with no percent sign. I can I accomplish > this. I have hundreds of rows of data that I need reformated in this way. > Thanks for your help.
From: Joe User on 8 Apr 2010 11:36
"SITCFanTN" wrote: > I have an Excel Spreadsheet and the data is > in General format and displays with one decimal > and with the percent sign like this: 85.1% or 96.3%. > I need to use this data in calculations so I need > it to be a number format as 85.1 or 96.3...with no > percent sign. I can I accomplish this. How do you accomplish this manually? Your description is subject to interprertation. Explaining your manual process might help to resolve the ambiguities. If you enter literally 10% into a cell that is formatted General, Excel will automagically change the format to Percentage. The value in the cell is actually 0.1. It can be used directly in other calculations; the appearance of "%" has no adverse impact. In fact, this is the best form because the calculation can be of the form A1*C1 instead of A1*C1/100, where C1 contains the percentage. If you are saying that the cell format is still General after entering the value, and the value appears as "10%", I would guess that you actually have the text string "10%". That may or may not cause computational difficulties in other formulas, depending on those formulas. > I have hundreds of rows of data that I need reformated in this way. Assuming the latter (cell with text that is formatted General), one way to rectify this for "hundreds of rows of data" is: 1. Put the number 1 into some cell, and copy it. 2. Select the cells to be changed, right-click, and click Paste Special > Multiply > OK. 3. While those cells are still selected, right-click, and click Format > Number > Percentage. That converts the numeric string into a number formatted as Percentage. If the cells contents are already numbers formatted as Percentage, and you wish them to scale them by 100 and remove "%" (not recommended), the procedure is similar. In Step 1, use 100 instead of 1. In Step 3, click Format > Number > Number or General instead of Percentage. Those procedures are tentative, subject to your clarification of the situation. ----- original message ----- "SITCFanTN" wrote: > I feel silly asking this because I know it should be easy, but I just can't > figure this out. I have an Excel Spreadsheet and the data is in General > format and displays with one decimal and with the percent sign like this: > 85.1% or 96.3%. I need to use this data in calculations so I need it to be a > number format as 85.1 or 96.3...with no percent sign. I can I accomplish > this. I have hundreds of rows of data that I need reformated in this way. > Thanks for your help. |