number formats and masks

Hi - I have a list of numbers whose formats i need to 
change.  For example, the number 79.26 shows up in my 
formula bar as 79.25999999.  How do I make all those 9's 
go away?
0
anonymous (74722)
12/2/2003 2:12:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
275 Views

Similar Articles

[PageSpeed] 5

Mike,

Format as a number to 2 dec places, menu Format>Cells and so on.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mike" <anonymous@discussions.microsoft.com> wrote in message
news:0b2901c3b8de$41876a70$a301280a@phx.gbl...
> Hi - I have a list of numbers whose formats i need to
> change.  For example, the number 79.26 shows up in my
> formula bar as 79.25999999.  How do I make all those 9's
> go away?


0
bob.phillips1 (6510)
12/2/2003 2:22:34 PM
Bob,
thanks, but it doesn't work.  Though the number displays 
and prints to two decimal places, the actual contents of 
the cell is the larger number 79.2599999999.  Its 
troublesome for me because I'm converting my excell file 
to text and importing it into my accounting system and its 
showing up there out of balance.

>-----Original Message-----
>Mike,
>
>Format as a number to 2 dec places, menu Format>Cells and 
so on.
>
>-- 
>
>HTH
>
>Bob Phillips
>    ... looking out across Poole Harbour to the Purbecks
>(remove nothere from the email address if mailing direct)
>
>"mike" <anonymous@discussions.microsoft.com> wrote in 
message
>news:0b2901c3b8de$41876a70$a301280a@phx.gbl...
>> Hi - I have a list of numbers whose formats i need to
>> change.  For example, the number 79.26 shows up in my
>> formula bar as 79.25999999.  How do I make all those 9's
>> go away?
>
>
>.
>
0
anonymous (74722)
12/2/2003 5:15:38 PM
Use 2 decimal places and precision as displayed under
tools>options>calculations,
the drawback is that it affects all decimals, otherwise you could use a help
column and round

=ROUND(A2,2)

copy down/across, copy and paste special as values in place and use the help
column instead.
There might still be some discrepancies due to the way excel and other
programs handle the decimals
but with some Andersen book keeping surely a few pennies can disappear <g>

for explanation and workarounds see

http://www.mcgimpsey.com/excel/pennyoff.html

for a very pedagogic description

-- 

Regards,

Peo Sjoblom


"mike" <anonymous@discussions.microsoft.com> wrote in message
news:072e01c3b8f7$e7bb4ab0$a001280a@phx.gbl...
> Bob,
> thanks, but it doesn't work.  Though the number displays
> and prints to two decimal places, the actual contents of
> the cell is the larger number 79.2599999999.  Its
> troublesome for me because I'm converting my excell file
> to text and importing it into my accounting system and its
> showing up there out of balance.
>
> >-----Original Message-----
> >Mike,
> >
> >Format as a number to 2 dec places, menu Format>Cells and
> so on.
> >
> >-- 
> >
> >HTH
> >
> >Bob Phillips
> >    ... looking out across Poole Harbour to the Purbecks
> >(remove nothere from the email address if mailing direct)
> >
> >"mike" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:0b2901c3b8de$41876a70$a301280a@phx.gbl...
> >> Hi - I have a list of numbers whose formats i need to
> >> change.  For example, the number 79.26 shows up in my
> >> formula bar as 79.25999999.  How do I make all those 9's
> >> go away?
> >
> >
> >.
> >


0
terre08 (1112)
12/2/2003 6:07:40 PM
Mike,

Okay,  if you actually want 79.26 rather than just see it, you have to get
that value in another cell, such as in B1
=ROUND(A1,2)

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mike" <anonymous@discussions.microsoft.com> wrote in message
news:072e01c3b8f7$e7bb4ab0$a001280a@phx.gbl...
> Bob,
> thanks, but it doesn't work.  Though the number displays
> and prints to two decimal places, the actual contents of
> the cell is the larger number 79.2599999999.  Its
> troublesome for me because I'm converting my excell file
> to text and importing it into my accounting system and its
> showing up there out of balance.
>
> >-----Original Message-----
> >Mike,
> >
> >Format as a number to 2 dec places, menu Format>Cells and
> so on.
> >
> >-- 
> >
> >HTH
> >
> >Bob Phillips
> >    ... looking out across Poole Harbour to the Purbecks
> >(remove nothere from the email address if mailing direct)
> >
> >"mike" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:0b2901c3b8de$41876a70$a301280a@phx.gbl...
> >> Hi - I have a list of numbers whose formats i need to
> >> change.  For example, the number 79.26 shows up in my
> >> formula bar as 79.25999999.  How do I make all those 9's
> >> go away?
> >
> >
> >.
> >


0
bob.phillips1 (6510)
12/2/2003 6:07:42 PM
Perfect.  Many thanks.  Its exactly what I needed.
>-----Original Message-----
>Mike,
>
>Okay,  if you actually want 79.26 rather than just see 
it, you have to get
>that value in another cell, such as in B1
>=ROUND(A1,2)
>
>-- 
>
>HTH
>
>Bob Phillips
>    ... looking out across Poole Harbour to the Purbecks
>(remove nothere from the email address if mailing direct)
>
>"mike" <anonymous@discussions.microsoft.com> wrote in 
message
>news:072e01c3b8f7$e7bb4ab0$a001280a@phx.gbl...
>> Bob,
>> thanks, but it doesn't work.  Though the number displays
>> and prints to two decimal places, the actual contents of
>> the cell is the larger number 79.2599999999.  Its
>> troublesome for me because I'm converting my excell file
>> to text and importing it into my accounting system and 
its
>> showing up there out of balance.
>>
>> >-----Original Message-----
>> >Mike,
>> >
>> >Format as a number to 2 dec places, menu Format>Cells 
and
>> so on.
>> >
>> >-- 
>> >
>> >HTH
>> >
>> >Bob Phillips
>> >    ... looking out across Poole Harbour to the 
Purbecks
>> >(remove nothere from the email address if mailing 
direct)
>> >
>> >"mike" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:0b2901c3b8de$41876a70$a301280a@phx.gbl...
>> >> Hi - I have a list of numbers whose formats i need to
>> >> change.  For example, the number 79.26 shows up in my
>> >> formula bar as 79.25999999.  How do I make all those 
9's
>> >> go away?
>> >
>> >
>> >.
>> >
>
>
>.
>
0
anonymous (74722)
12/2/2003 7:27:56 PM
Reply:

Similar Artilces:

Auto number items
I am looking to put a number in a all of the different types of boxes on my diagram. I have been creating a text box with a number and dragging it to the box. I was in hopes that there might be an automated process. Thanks Chip Asmus Have you tried Tools/Addons/Visio Extras/Number Shapes... "Chip Asmus" <Chip@lawlershallmark.com> wrote in message news:eaZ0rwfYHHA.1244@TK2MSFTNGP04.phx.gbl... >I am looking to put a number in a all of the different types of boxes on my >diagram. I have been creating a text box with a number and dragging it to >the box. I ...

Error Number: 0x800ccc0e when I try to get messages
Hello, I am a postmaster of our network and I am having some problems with Outlook 2000 and our pop3 server. The problem is that randomly, Outlook's users can not download their mail because they get the error number 0x800ccc0e when they try to send amd receive mail. We are using Microsoft Outlook 2000 SP-3 (9.0.0.6627) and our server is a Linux Debian running a 2.4.27 kernel. I have tried with ipop3d and in.qpopper as a pop3 daemons and with both I get the error. There is no firewall between them and this is the tcpdump capture from the server (our pop3 server runs at port 40110, bu...

Charting with concatenated numbers and text
I am scatter charting from columns of data (text and numbers) that I concatanate into a long string so that I can use the validate drop down list feature in Excel, which only supports one column. I use Mid, Left and Right functions to then pick the relevant charting data from the string. Unfortunately although the numbers look good, Excel does not treat them the same way that it does with directly entered numbers - and my charts do not display accurately. Any ideas to to fix this problem or suggest a modified workflow. I am not an advanced user but would like to use the drop down li...

Count and display number of items
Hi there, I have one column of telephone numbers, please can someone help me with a formula to create another column showing how many occurrences there are of each number. Thanks! Andy. Andy, A pivot table can do just that. Set the data function to COUNT, if not already. To identify the duplicates, you can sort your table on the telephone number column, and put in a helper column: =if(A3=A2, "Dup","") Copy down. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Andy" <wiltshireguyuk@yahoo.co.uk&...

Conditional formatting in empty cells
Using Conditional Formating from the Format menu I am setting the Pattern for cells containinig a number greater than 6 to Green. This works fine except empty cells i.e. containing no data, go green. Has any one got any suggestions? Best wishes Nick Are you sure the cell is empty? Or do you put a space character in that cell--or do you have a formula that evaluates to ""? If it's a formula that evaluates to "", you could use a rule like: Formula is: =AND(ISNUMBER(A1),A1>6) Nick Horn wrote: > > Using Conditional Formating from the Format menu I am s...

format end notes to appear at the chapter, not end of document?
I need my end notes to appear at the end of each chapter. Word puts them at the end of the document. Can I do this manualy, or is there a shortcut format I can use? Right-click an endnote and choose Note Options from the context menu. At "Location," choose "End of document." Make sure that "Apply changes to" reads "Whole document"; then click Apply. -- Stefan Blom Microsoft Word MVP "Liz" <Liz@discussions.microsoft.com> wrote in message news:B9569343-3363-45DD-A61A-9990C9689443@microsoft.com... >I need my e...

Formatting and Styles throughout
I have a question for you about formatting and styles. I've used these in many programs and am well-versed using them in Word. I have now to do so in Publisher and am new to the program. The question is: how are styles most easily applied in a large document when things like headers and then body-text are grouped in style but not location. For example, a list of features and their operation. The headers and body-text are mixed throughout. Do I create a separate text file for each feature heading and then a separate file for each text body that is associated? If so, how do I keep them lin...

Payroll Check format in Report Writer
Currently on version 9, use direct deposit. I have modified the Direct Deposit Employee Check Other-L Direct Deposit Employee Checks stub on top and bottom-L and my changes do not appear when I print, I have check the security settings. I have modified the Direct Deposit Statement of Earnings and those appear OK. When I look in payroll setup my default check is 'Other-Single Feed' Am I modifying the wrong report in Report writer for this check? thanks -- Doug What is the setting for printing the direct deposit advices? As separate statements or part of the checks? If doing ...

Formatting time in cells
Is it possible to display only the hour element of a time. i.e. 14.05:32PM displays as 14.00 or something simila -- Message posted from http://www.ExcelForum.com Format>Cells>Custom. In the Type box, enter -- Kind Regards, Niek Otten Microsoft MVP - Excel hh "Alan T >" <<Alan.T.11f3hj@excelforum-nospam.com> wrote in message news:Alan.T.11f3hj@excelforum-nospam.com... > Is it possible to display only the hour element of a time. > > i.e. 14.05:32PM displays as 14.00 or something similar > > > --- > Message posted from http://www.ExcelFor...

Total number of pages
I would like to include total # of pages in the footer of my Publisher 2003 document (Master page). How do I do this? Do you mean something like: Page 10 of 20? Simply type of 20 after the # -- Mary Sauer http://msauer.mvps.org/ "Lynn" <Lynn@discussions.microsoft.com> wrote in message news:DAFBED91-A28F-42CC-B621-1C355476F79E@microsoft.com... >I would like to include total # of pages in the footer of my Publisher 2003 > document (Master page). How do I do this? ...

Put a single quotation mark in front of numbers in selected cells
Dear Expert: I need to convert numbers to text and put a tick (') mark/single quotation mark in front of every one of them. I would like to have a macro solution and the macro should work ONLY on selected cells. Help is much appreciated. Thank you very much in advance. Regards, Andreas This does exactly what you asked for... Sub AddApostrophe() Dim C As Range For Each C In Selection C.Replace C.Value, "'" & C.Value Next End Sub However, you do not need to put an apostrophe in front of each number to convert it to text, you can use th...

How do I print a select number of address records from Access
I'm trying to print just a select number of records for a mailing that I'm doing. I can't seem to figure out how to do this. Thanks. On Thu, 8 Nov 2007 12:23:04 -0800, robbie6 <robbie6@discussions.microsoft.com> wrote: >I'm trying to print just a select number of records for a mailing that I'm >doing. I can't seem to figure out how to do this. Thanks. Create a Query based on the table containing the addresses (and perhaps other tables). View the query's Properties (with the View menu item, or rightclick the background behind the table icons and sele...

Formatting cells to display grams
How do I format cells in Excel to display different weights? -- jamie81 ------------------------------------------------------------------------ jamie81's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18516 View this thread: http://www.excelforum.com/showthread.php?threadid=376698 Examples...? Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10470 View this thread: http://www.excelforum.com/showthread.php?threadid=...

Files opening with a number appended
Hi all, Hope you can help. I have a Macro in an excel file that looks for and opens another named excel file based on some variables. For example a new spreadsheet is opened today called 100305 (based on the date), the macro will then look for a file from the previous week (030305), open it, look for a certain value and copy it into the new sheet. However from time to time I'm finding that when the macro opens the previous file it appends a '1' to the end of the name (eg 0303051) and therefore my macro fails as it can't reference the file properly in order to find the requi...

Formatting a date field
Is it possible to format a date field in excel such that :- 1) it accepts day, month, year or month, year or just year or blank and 2) you are able to sort properly on the field Bob M Formatting has no effect on XL's input parser. You may find you can work with this: http://cpearson.com/excel/DateTimeEntry.htm In article <f1pb6t$bik$1@lust.ihug.co.nz>, "Bob Matthews" <matthews@es.co.nz> wrote: > Is it possible to format a date field in excel such that :- > 1) it accepts day, month, year or month, year or just year or blank and > 2) you are able...

Chart with conditional formatting
I have a spreadsheet that I am using to keep track of the RSVPs to a banquet. The spreadsheet has the standard columns such as first name, last name, company etc, I have also added columns for table # (the table I have assigned them for seating at the banquet), a Y/N column to indicate if we have received payment for the banquet and a Y/N column that shows if they are a VIP or Speaker. What I would like to do is create a chart using conditional formatting in worksheet cells. I envision having a row for each table number, then a column that would represent each seat at the table (each ta...

how to create item numbers column in a Query
Is there anyway to place a column (Item#) in a query, which will show the item numbers of the results. Say that, if query result has 13 items, Item# values will be sequentially 1,2,3...11,12,13. I have a feeling that it might be solved by usind DCOUNT function. Thanks ...

Cell Formating Problems
This is what I do: I open up a brand new worksheet. I format cell A1 to text. Then I enter the number 1 into it. Then I format the cell to Numeric and the 1 in the cell does not change. it stays as (left justified 1. If I delete the 1 and type a 1 into that cell and hit Enter, it become 1.00 (a number). Whats happening here??? Why doesn't it convert the 1 to a 1.00 when format the cell to numeric? Why do I have to re-enter the number -- Message posted from http://www.ExcelForum.com brain It was originally entered as Text. Simply re-formatting doesn't change its underlying ...

print number of update records through sql job
I have update statement which updates column name (auditor_details) in a churn of 5000 records at a time of auditor table which contain 3.2 millions records. I have schedule this through sqljob. I'm looking over sql example to check for update records and gives a output and quit the jobs if no update records. Thanks in advance sqlnovice (sqlnovice@discussions.microsoft.com) writes: > I have update statement which updates column name (auditor_details) in a > churn of 5000 records at a time of auditor table which contain 3.2 > millions records. I have schedule this...

Format cell to display numbers instaed of ###'s
Not sure how to querry this in the KB.... but I want to be able to have a number such as 4 1/8 in a cell that is set at width less than what is required to display it properly. Same with a date/time entry. In cells where I'm entering text, the text will "spill" over if the adjoining cells are empty of data. No so with number's. How do I change it so it will display as 4 1/8 without widening the cell? If I place an apostrophe ' in front of the number, or format the cell as plain text it will work, but I really need to retain the numeric value. Thanks, David... ...

Exchange 2003 reports
Is this possible with Exchange 2003? Or, is it only available through a third party reporting tool? If it is available could you please tell me how, if not, could you reccommend an acceptable third party tool? On 17 Jan 2007 07:14:19 -0800, JoshRountree@gmail.com wrote: >Is this possible with Exchange 2003? Or, is it only available through a >third party reporting tool? If it is available could you please tell me >how, if not, could you reccommend an acceptable third party tool? Not out of the box with Exchange. You'll want something like Promodag or Spotlight Mark Arnold [M...

problem with adding column, formatting
I saved a workbook in quatro pro 8 to excel format and then opened it in excel (ms office 2000). some of the columns will not permit reformatting - changing the date format or changing from currency to accountant number format. I can not add blank columns between columns containing data. A window pops up indicating that Excel will not insert a column as I will lose data. I have attempted to follow instructions in the documentation and in the help screen but can not insert blank columns. In the transfer process does excel end up with locked column and row structure and/or formatting of so...

How to put names over columns and left-justify numbers or align decimal points
I already have made the spreadsheet and want to put names over the columns without making a new row for the names. Is this possible? Like I don't wantt A1 B1 etc. to change to A2 B2 etc. when I put the text above the columns. Then I want to save it as an .htm file so that the tables will have headings like what I have put over the columns. The columns will have to get a little wider to accommodate the headings over them. I need to left-justify the numbers in the columns or line up the decimal points vertically. This is not currency, if that matters. Ike wrote... >I already hav...

Open CSV files without auto-formatting values
Hello, I am having difficulties while opening CSV files: If there is value i.e "6.1" which is decimal, Excel interprets it as date = 01. June (According to regional date settings) If there is value "16.04.07" which is actually date 2007-04-16, Excel again converts it according to regional settings, and finally there is 2016.04.07 Is there any settings or macros available, so I can open CSV file, and treat each column just as TEXT ? without any auto-guessings about cell type. Import wizard is not good solution this time. Also playing with regional settings is not ...

Page format problems
Page prints differently when spreadsheet is printed from desktop using WIN98SE (Excel 2000). Document was created in Excel 2000 on a desktop using WIN2000. Could someone help and explain why. Printer is LJ5Si. Do we need to upgrade to WIN2000 on all desktops. Running NT4.0 on server???????? Thanks Is it stuff like margins being slightly off? If yes, then I'm gonna guess that it's a printer driver difference (and that's just a guess). I bet that Win98 and win2k have different drivers (even for the same printer). You may want to visit the HP site and see if there's...