Showing 0 after decimal with ROUND

Hi,

I'm using the ROUND function like this:

=ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)

Unfortunately, if the numeral after the decimal is a 0, then it doesn't 
display.  How can I force a 0 to display after the decimal?

Libby
0
Utf
12/4/2009 1:44:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
1027 Views

Similar Articles

[PageSpeed] 34

Format the cell as Number with 1 decimal place - it would appear to be
formatted as General.

Hope this helps.

Pete

On Dec 4, 1:44=A0pm, Libby <Li...@discussions.microsoft.com> wrote:
> Hi,
>
> I'm using the ROUND function like this:
>
> =3DROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
>
> Unfortunately, if the numeral after the decimal is a 0, then it doesn't
> display. =A0How can I force a 0 to display after the decimal?
>
> Libby

0
Pete_UK
12/4/2009 1:59:38 PM
Slap a text around it and play until you like it. This achieves what i 
believe you want:
=TEXT(ROUND(C29*(92%),1),"##.0") & TEXT(-ROUND(C29*(108%),1),"##.0")
-- 
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what 
is helpful. 


"Libby" wrote:

> Hi,
> 
> I'm using the ROUND function like this:
> 
> =ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
> 
> Unfortunately, if the numeral after the decimal is a 0, then it doesn't 
> display.  How can I force a 0 to display after the decimal?
> 
> Libby
0
Utf
12/4/2009 2:11:01 PM
He's using text concatenation, so the format in the cell won't help, Pete.

=TEXT(ROUND(C29*92%,1),"0.0") & TEXT(- ROUND(C29*108%,1),"0.0")
or perhaps
=TEXT(ROUND(C29*92%,1),"0.0") &"-"& TEXT(ROUND(C29*108%,1),"0.0")
depending on how you want to treat zeroes and negative numbers.
--
David Biddulph


"Pete_UK" <pashurst@auditel.net> wrote in message 
news:7d36eb67-f1da-4065-be14-1ab1fc61b1bb@c3g2000yqd.googlegroups.com...
Format the cell as Number with 1 decimal place - it would appear to be
formatted as General.

Hope this helps.

Pete

On Dec 4, 1:44 pm, Libby <Li...@discussions.microsoft.com> wrote:
> Hi,
>
> I'm using the ROUND function like this:
>
> =ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
>
> Unfortunately, if the numeral after the decimal is a 0, then it doesn't
> display. How can I force a 0 to display after the decimal?
>
> Libby


0
David
12/4/2009 2:16:53 PM
Hi,
highlight the range, right click in the mouse, format cells, custom format

to show one decimal enter

0.0

to show 2 decimals

0.00

"Libby" wrote:

> Hi,
> 
> I'm using the ROUND function like this:
> 
> =ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
> 
> Unfortunately, if the numeral after the decimal is a 0, then it doesn't 
> display.  How can I force a 0 to display after the decimal?
> 
> Libby
0
Utf
12/4/2009 2:17:01 PM
Brilliant, John Bundy!  Thanks a lot :)

"John Bundy" wrote:

> Slap a text around it and play until you like it. This achieves what i 
> believe you want:
> =TEXT(ROUND(C29*(92%),1),"##.0") & TEXT(-ROUND(C29*(108%),1),"##.0")
> -- 
> -John http://www.jmbundy.blogspot.com/
> Please rate when your question is answered to help us and others know what 
> is helpful. 
> 
> 
> "Libby" wrote:
> 
> > Hi,
> > 
> > I'm using the ROUND function like this:
> > 
> > =ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
> > 
> > Unfortunately, if the numeral after the decimal is a 0, then it doesn't 
> > display.  How can I force a 0 to display after the decimal?
> > 
> > Libby
0
Utf
12/4/2009 2:37:02 PM
That doesn't work with text strings, Eduardo.
--
David Biddulph

"Eduardo" <Eduardo@discussions.microsoft.com> wrote in message 
news:97E418FF-B471-495B-8AF0-71DE9A2AEAD5@microsoft.com...
> Hi,
> highlight the range, right click in the mouse, format cells, custom format
>
> to show one decimal enter
>
> 0.0
>
> to show 2 decimals
>
> 0.00
>
> "Libby" wrote:
>
>> Hi,
>>
>> I'm using the ROUND function like this:
>>
>> =ROUND(C29*(92%),1) &- ROUND(C29*(108%),1)
>>
>> Unfortunately, if the numeral after the decimal is a 0, then it doesn't
>> display.  How can I force a 0 to display after the decimal?
>>
>> Libby 


0
David
12/4/2009 6:41:33 PM
Reply:

Similar Artilces:

Round up a decimal in a query field
Hopefully this is an easy question. How do you round up a number in a query field? Excel has the ROUNDUP function, but I can't find anything similar in Access. Can anyone help? Function:round([tablename!fieldname,2]) I think. It may be round([tablesname!fieldname],2) "bigomega73" wrote: > Hopefully this is an easy question. How do you round up a number in a query > field? Excel has the ROUNDUP function, but I can't find anything similar in > Access. Can anyone help? Thanks Golfinray, but that only rounds to the nearest decimal place. What I want is the numb...

Upgrading to 9.0 on Windows Server 2003 64bit Edition
We are upgrading from 7.0 to 9.0. We have new SQL Servers on which we will perform the upgrade and move into production once complete replacing our existing servers. These SQL Servers are running Windows Server 2003 Enterprise x64 Edition. When attempting to install either GP v7.0 or v7.5 the error message "The image file ...\CDSetup.exe is valid, but is for a machine type other than the current machine." is presented and the installation script will not initialize. Is it possible to install GP 7.0 or 7.5 on the 64bit version of Windows Server 2003 or must we install the 32 bit v...

GP 10.0 Fresh Installation
I am trying to install GP 10.0 Trial Version on my laptop. Once when I tried half-way through the installation I had to abort installation. Subsequently after uninstalling all the installed component I restarted installation. Pre-requisite component installation window appears and installs Dextrity components. Next when I try installation using Autorun.inf file I get error "Installation Failed". If I try invoking Setup.exe directly nothing happens. Can someone help me? Hi pvravindran If this is a French Canadian Installation, I would recommend you contact Strophe (www.strop...

Reg: Tracking emails into MS CRM3.0
Hi Just now I installed MS CRM 3.0 desktop outlook client in my system. I am unable to track more than 1 email into CRM at a time from my Outlook inbox. Is there any way to track more than 1 email into CRM? please let me know......... Thx in advance. Anil V no, you can only do one at a time. If you need to track a lot of e-mail, you should investigate the CRM Exchange Router which will automatically post e-mail for you. Dave Ireland "Anil Kumar V" <AnilKumarV@discussions.microsoft.com> wrote in message news:9E965A49-5558-4D7E-836F-A80092F57F30@microsoft.com... > Hi ...

pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but not for others... there are no differences in the data layout, yet this happens? any clues as to why? and how I can get the grand totals to show for all fields. thanx in the PivotTable floating toolbar, click PviotTable, then Table Options, select 'Grand totals for columns' option. ...

ZIP codes beginning with 0
I'm having trouble getting ZIP codes beginning with 0 to stick, even when specifying ZIP code in the format category type. No matter what I do, the initial 0 always gets dropped. Advice? Thanks. Hi Peter.......... Either pre-format your cells as TEXT, or enter a leading apostrophe before entering the number with the leading 0........the apostrophe won't show, it only forces TEXT formatting on that cell.......... Vaya con Dios,] Chuck, CABGx3 "Peter" <anonymous@discussions.microsoft.com> wrote in message news:2ab7001c46836$3382cf70$a601280a@phx.gbl... > I&...

Do I need DSClient to run Exchange 5.5 on an NT4.0 box in Windows 2003 Native Mode ADS?
Good Evening, I am in the process of migrating my WinNT4.0 domain and Exchange 5.5 Org to Windows 2003 ADS/Exchange 2003. I know best practice is to change the domain to Native mode, but how does this affect my NT4.0 server running 5.5? Do I need to simply load the DSClient onto the server? I should also note that we did an inplace upgrade from NT 4.0. This is a single domain environment. As long as you no longer have NT 4.0 BDCs you should be able to move to native mode. Your NT 4.0 server running E55 will not be affected (unless of course it is also a BDC). It is a good idea to...

Decimal key on keypad is ignored
In Excel 2000 with fixed decimal 2 places in the setup, I could enter 10+. and the cell would read 10.00. In 2003, the decimal is ignored and I must enter 1000 to get 10.00. Is there a way to fix this change? The functionality has not changed in Excel 2003, the fixed decimal is overridden if you manually enter the decimal. I would check a couple of things 1) Your decimal separator under windows regional settings is . 2) If you are wanting to use the system separators, as above, you make sure that setting is checked in Excel Tools>Options>International>Use system settings Of...

Appointment shows error
I send meeting reminder to my collegue, but they could not open the item. OLXP and 2003 users could open but OL 2000 users still have the error.Whats wrong- it is showing an error. I use ol xP with windowsXP. Is it their problem or with my OLXP. can u advise me? Microsoft offers a hotfix for Outlook 2000 to improve its compatibility = with iCal/vCal 2.0 meeting requests. See = http://support.microsoft.com/?kbid=3D823199 . Alternatively, have = Outlook 2002/2003 senders avoid using reminders in the meeting requests = they send to Outlook 2000 recipients.=20 --=20 Sue Mosher, Outlook MVP...

CRM3.0 Question on Activity
Hello, I am using CRM3.0. I have the following case. 1. Create Campaign 2. Add contact to campaign list 3. Create Campaign activity - Phone call 4. Distribute activity 5. CRM User will make phone call 6. When contact is interest, CRM user will make a "Free Trail" for contact. I am having troble in step 6. So far as I know, I have two choices for "Free Trail". A) Service Activity If I choose to create a service activity, then I can not link up the service activity with the campaign or phone call. B) Appointment If I choose appointment, I can set "regarding"...

MS CRM 3.0 Small B. ed. modules
We have installed Small business server on one computer with all requirements fullfilled. The problem is: we have available only 3 of 5 (or 6) modules: service, settings and workplace. The other 2 - marketing and sales (wich we consider to be the most important) are not available (we can't see them). Does anybody know how to solve the problem? Thank you! Hello, may be i have a solution for you. You have to check the other modules in the following menu: - startpage of CRM - extra - options (--> a new page will be shown) Then click on workplace: there you can...

unread email shows 3 envelopes
strangest thing....my unread/read emails shows three envelopes instead of one...I have the latest virus scan and checked for virus known found, checked for virus with mcafee online(free) nothing...have the latest office update and windows...I don't get any wierd errors or bsod. can anybody help appreciate it very much. >-----Original Message----- >strangest thing....my unread/read emails shows three >envelopes instead of one...I have the latest virus scan >and checked for virus known found, checked for virus with >mcafee online(free) nothing...have the latest offic...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

MS POS 1.0 Install error...
Im using XP PRO SP2 ..net 1.1 installed MS SQLServer installed When i try and install MS Point of Sale 1.0 , i get this error message at the end of the install, when it says "Installing and configuring database" ------Error Message------ System.ComponentModel.Win32Exception: The system cannot find the file specified at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo) at System.Diagnostics.Process.Start() at Microsoft.Rms.Installation.CustomActions.Utilities.OpenSqlPortInFirewall(String installPath, String portNumber, String& errorMessage...

CRM 4.0 Terminal Services support
I have read through the CRM 4.0 planning guide and the "what's new in CRM 4.0" partner guide, and see very little mention of Terminal Services support for the client Outlook 2003/2007 CRM Plugin. One example in the Planning Guide page 4-63 shows a "Terminal Server, Microsoft Dynamics CRM for Outlook" in "Domain B - Forest Y". There also appears to be a discrepancy between these two guides in support for Windows 2000 SP4 - the what's new page 2-11 specifically says it is supported, and the planning guide page 3-29 specifically says it is not. Is t...

CListCtrl InsertColumn(0, "L", LVCFMT_RIGHT)?
Hi, The CListCtrl doesn't appear to like the first column to be aligned non-left. Why? By inserting the first column with InsertColumn(1, "L", LVCFMT_RIGHT), it does show it right aligned. But what if there are columns already? Should I delete and reinsert them all? "Olaf van der Spek" <Olaf@XCC.TMFWeb.NL> wrote in message news:%23K2rr8LLFHA.2716@TK2MSFTNGP15.phx.gbl... > Hi, > > The CListCtrl doesn't appear to like the first column to be aligned > non-left. Why? > By inserting the first column with InsertColumn(1, "L", LVCFMT_R...

I need to do a slide show on information technology careers
where is a good place where I can find pictures as well as information for a slide show You can find lots of photographs in the Clip Art section of http://office.microsoft.com -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "c hill" <c hill@discussions.microsoft.com> wrote in message news:358D5DB0-5B91-447A-B0F3-F2C81203D091@microsoft.com... > where is a good place where I ca...

how to generate xsd for sqlxml 3.0
Hi guys, How to generate this xsd schema below for SqlXml <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <ElementType name="Employee" sql:relation="Employees" > <AttributeType name="EmpID" /> <AttributeType name="FName" /> <AttributeType name="LName" /> <attribute type="EmpID" sql:field="EmployeeID&q...

Publisher v 2.0
I have some files from an old 3.1 system that I would like to import into a newer v of Publisher. Can I open them with Publisher 2003 etc? You should be able to open them... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bill" <anonymous@discussions.microsoft.com> wrote in message news:081001c4e5f5$c7f92a40$a501280a@phx.gbl... >I have some files from an old 3.1 system that I would > like to import into a newer v of Publisher. Can I open > them with Publisher 2003 etc? >-----Original Message----- >...

Format a cell to display decimal hours.
How do I format a cell to display decimal hours? Instead of displaying 3:45 (for 3 hours, 45 minutes), which is one of the standard formats, I'd like to display 3.75 (hours). h.hh doesn't do it. The above is formatting for an elapsed time calculation (finish time minus start time). (On a "time card") Excel 2000. Will a later Excel do it for me? Some good source for a parsing function? Any good reference? Thanks, Fred Holmes Fred, Format the cell in #.## format and multiply your result by 24. http://HelpExcel.com "Fred Holmes" wrote: > How do I forma...

Hyperlinks show code when Composing
Something changed in my settings and now when I'm composing my e-mails, the destination address shows up in my text body instead of the hyper link that I created. How can I change it back? Example: I get {HYPERLINK "http://www.microsoft.com"} instead of my Hyperlink phrase being highlighted in blue & underlined: Click here to go to the Micorsoft website While composing go to the Tools menu > Options > View tab > Uncheck Field Codes. "Scott" <Scott@discussions.microsoft.com> wrote in message news:B3810D6A-C8AB-4184-9BFF-DFBEC41D6326@micros...

How do 1 set up square or round labels 12 per sheet in Publisher?
I'm trying to use Avery 5294 and can't seem to manipulate publisher to set up 12 squares or rounds 3 across x 4 down. gbdonart wrote: > I'm trying to use Avery 5294 and can't seem to manipulate publisher > to set up 12 squares or rounds 3 across x 4 down. ============================ What version of Publisher are you using? In Pub 2003 you could try the following: Create one label and go to...File / Page Setup... Choose... Avery J8164 Avery L7164 On the print screen you can make adjustments if you click the "Adjust Label Printing" button. FWIW.....

excel 4.0 macro removal tool
Dear all, i am looking for excel 4.0 macro removal tool from excel 97 file.Actually we are facing such problem from two months and it gives pain.some excel files contain macro of excel 4.0 with "very hidden and very protective" attribute so that we cannot remove it.i have searched on net and found that there are some tools called macro remover which can remove all types of macros from excel file including excel 4.0 macro from excel 97 file,but it charges which i cannot afford.so if there any tool that can remove particular excel 4.0 macro from excel 97 file please let me know...

eConnect 10.0 SDK (version prior to SP1 & SP2)?
Hi All, I am reposting this question, as I am yet to succeed in finding the correct link to this download. I need eConnect 10.0 SDK, version prior to SP2, since SP2 require the basic version to be present. And I do not have this installed. I checked PartnerSource, but it is leading me to SP2 install page. I sincerely hope someone assist me in finding the correct link in PartnerSource. -- Regards, Vaidy https://mbs.microsoft.com/customersource/downloads/servicepacks/eConnect10DownloadsforMicrosoftDynamicsGP10.htm "Vaidy" wrote: > > Hi All, > > I am repostin...

Problem rounding down Access 2000
The abridged version: I want to divide an odd whole number by 2 and deal seperately with the quotient as a whole number & remainder as a decimal. If I use integers to perform the calculations the quotient is wrong because it automatically rounds up. (Sample code at bottom) How can I get the quotient to round down? The detailed version: I have build an application to generate travelers for my company. A traveler accompanies our products through the manufacturing process. The first traveler is always the sample traveler. Each traveler after that has a traveler quantity...