Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000 row
I have a spreadsheet with a column of numbers that need formated to currency
without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000 row
You want them rounded to the nearest Integer?
Enter this in B1 =ROUND(A1,0) and copy till the end of your dataset [you can take your cursor to the bottom right of B1 and double click when curson changes to the plus sign to copy it down... assuming there are no blank rows] Once you get what you want you can cut Col B and PASTE SPECIAL|Values over Col A... Or Paste Special in place and delete Col A... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000 row
Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal
places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
I cant round the numbers to the nearest integer because they are not
consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Barbara wrote on Mon, 13 Oct 2008 07:56:08 -0700:
I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. I can't understand what's going on. I took your numbers, chose the column Formatcellscurrencydecimal places:2 My results were $18.00, $0.95, $0.75 -- James Silverton Potomac, Maryland Email, with obvious alterations: not.jim.silverton.at.verizon.not |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Also there is formulas in some of the cells with my number list in it. I try
to copy and paste special the column so that only VALUES are pasted, but the numbers change. Are the forumlas causing me issues? "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Barbara,
The number you see as 18 is actually 17.71 that is why when you change the format to currency it shows as 17.71. If you select the cell showing 18 and look in the EDIT BOX you will see it shows 17.71 or something similar... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Barbara,
That might be the reason... the formula may be showing the number as 18 even when the underlying number is 17.71 James, The number displayed is 18 but underlying number is 17.71 so it changes when formatted with Currency format. Barbara wants it to be $ 18.00 -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: Also there is formulas in some of the cells with my number list in it. I try to copy and paste special the column so that only VALUES are pasted, but the numbers change. Are the forumlas causing me issues? "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Is there any way to format what is showing?
Barbara "Sheeloo" wrote: Barbara, The number you see as 18 is actually 17.71 that is why when you change the format to currency it shows as 17.71. If you select the cell showing 18 and look in the EDIT BOX you will see it shows 17.71 or something similar... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Toolsoptionscalculation and select precision as displayed
that means if you have a value that is in fact 17.71 but is displayed as 18 then it will be converted to that. It is pretty risky to use since calculations will not be exact -- Regards, Peo Sjoblom "Barbara" wrote in message ... Also there is formulas in some of the cells with my number list in it. I try to copy and paste special the column so that only VALUES are pasted, but the numbers change. Are the forumlas causing me issues? "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
You are presumably starting with a number of different cells which are
already formatted in different ways. If you want to split out those formatted currently with zero decimal places you might use =IF(CELL("format",A2)="F0",TEXT(A2,"$0"),TEXT(A2," $General")) or if you have a choice between zero decimal places and two decimal places, you could use =IF(CELL("format",A2)="F0",TEXT(A2,"$0"),TEXT(A2," $0.00")) Note that those would leave you with values as text, not numbers. Another option, if you are happy to lose the precision of the data (and have the value of 17.72 converted permanently to 18), would be to set Tools/ Options/ Calculation: Precision as displayed, and then use Format/ Cells/ Custom: $General This would leave your values as numbers, but merely lose you the precision. -- David Biddulph "Barbara" wrote in message ... I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
I tried it and it does ok until I try to format it with decimals.
Thanks for trying, Barbara "Peo Sjoblom" wrote: Toolsoptionscalculation and select precision as displayed that means if you have a value that is in fact 17.71 but is displayed as 18 then it will be converted to that. It is pretty risky to use since calculations will not be exact -- Regards, Peo Sjoblom "Barbara" wrote in message ... Also there is formulas in some of the cells with my number list in it. I try to copy and paste special the column so that only VALUES are pasted, but the numbers change. Are the forumlas causing me issues? "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Barbara,
As you would have seen by the different responses, there is no simple way to do it you want different things for different cells. David's formula will do what you want to do. You will need to convert them back to numbers then which you can do by entering 1 somewhere copying it selecting your column and choosing Edit|Paste Special|Multiply or you can send the file to me... I will do the needful and send it back... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: Is there any way to format what is showing? Barbara "Sheeloo" wrote: Barbara, The number you see as 18 is actually 17.71 that is why when you change the format to currency it shows as 17.71. If you select the cell showing 18 and look in the EDIT BOX you will see it shows 17.71 or something similar... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
Got it!
This is exactly what I did... 1) Set Precision as Displayed 2) Copy the column and did a paste special "Vaules Only" into a new sheet. 3) Formated to currency. David, you presumed correctly all the cells were different, some had formulas and some didnt. Some had 2 decimal places and some had none. The precision as displayed worked, but I couldnt format the existing column because of the formulas, I had to move the numbers to get them to format without changed to the exact number the formula was bringing back. Thanks so much to all, you saved us 17+ hours of work! Barbara "David Biddulph" wrote: You are presumably starting with a number of different cells which are already formatted in different ways. If you want to split out those formatted currently with zero decimal places you might use =IF(CELL("format",A2)="F0",TEXT(A2,"$0"),TEXT(A2," $General")) or if you have a choice between zero decimal places and two decimal places, you could use =IF(CELL("format",A2)="F0",TEXT(A2,"$0"),TEXT(A2," $0.00")) Note that those would leave you with values as text, not numbers. Another option, if you are happy to lose the precision of the data (and have the value of 17.72 converted permanently to 18), would be to set Tools/ Options/ Calculation: Precision as displayed, and then use Format/ Cells/ Custom: $General This would leave your values as numbers, but merely lose you the precision. -- David Biddulph "Barbara" wrote in message ... I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to Format Currency without changing values - over 16,0000
And what happens to the dollar signs when you do the Paste Special/
Multiply? -- David Biddulph "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Barbara, As you would have seen by the different responses, there is no simple way to do it you want different things for different cells. David's formula will do what you want to do. You will need to convert them back to numbers then which you can do by entering 1 somewhere copying it selecting your column and choosing Edit|Paste Special|Multiply or you can send the file to me... I will do the needful and send it back... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: Is there any way to format what is showing? Barbara "Sheeloo" wrote: Barbara, The number you see as 18 is actually 17.71 that is why when you change the format to currency it shows as 17.71. If you select the cell showing 18 and look in the EDIT BOX you will see it shows 17.71 or something similar... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I cant round the numbers to the nearest integer because they are not consistant at all, one might go up 2 cents, the other 30 cents. I just need Excel to stop looking at what isnt showing and format the showing number in currency. "Sheeloo" wrote: Or you can simply change the FORMAT|CELLS|Number|NUMBER with 0 decimal places... -- To get my email id paste my address in an Excel cell and press Enter... "Barbara" wrote: I have a spreadsheet with a column of numbers that need formated to currency without rounding. This is how the numbers are listed.... 18 0.95 0.75 This is what happens when I format to currency... $17.71 $0.95 $0.75 But I needed the 17.71 to be 18.00. There is no consistancy in the numbers in the column, some stay the same and some change. I dont need any of them to change values. Please help, otherwise we are changing 16k rows manually! Thanks so much, Barb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to change 000-000-0000 phone format to (000)000-0000 ???? | Excel Worksheet Functions | |||
Changing cell format from text to currency | Excel Worksheet Functions | |||
changing cell values with text and currency | Excel Discussion (Misc queries) | |||
Phone number format from 000.000.0000 to (000)000-0000 | Excel Discussion (Misc queries) | |||
Changing format of column from "general" to "currency" | Excel Discussion (Misc queries) |