Decimal Places

Hi,

I am currently having a problem with decimal places. 
Basically I don't want any whatsoever.

In a cell, I am doing division between 2 figures to find 
out how many items I can purchase with an amount of money.

So, the formula is 
AmountOfMoney/CostOfItem

If the ansewr was for example 63.97 then the results cell 
would show 64 (I have it set so that 0 decimals are shown)

There are then other calculations throughout the 
spreadsheet that use this result. However, since I can 
only afford 63 WHOLE items, I don't want the 0.97 to be 
included as part of the equation since this throws out all 
of my figures.

Is there any possible way that I can force excel to round 
a figure down or just use whole numbers? 

Many thanks in advance,
Simon
0
9/22/2003 5:20:18 PM
excel 39879 articles. 2 followers. Follow

3 Replies
266 Views

Similar Articles

[PageSpeed] 36

=rounddown(AmountOfMoney/CostOfItem,0)

or

=Trunc(AmountOfMoney/CostOfItem)

-- 
Regards,
Tom Ogilvy


"Simon Argent" <Simon.Argent@warnerbros.com> wrote in message
news:05c001c3812d$cb7a57d0$a101280a@phx.gbl...
> Hi,
>
> I am currently having a problem with decimal places.
> Basically I don't want any whatsoever.
>
> In a cell, I am doing division between 2 figures to find
> out how many items I can purchase with an amount of money.
>
> So, the formula is
> AmountOfMoney/CostOfItem
>
> If the ansewr was for example 63.97 then the results cell
> would show 64 (I have it set so that 0 decimals are shown)
>
> There are then other calculations throughout the
> spreadsheet that use this result. However, since I can
> only afford 63 WHOLE items, I don't want the 0.97 to be
> included as part of the equation since this throws out all
> of my figures.
>
> Is there any possible way that I can force excel to round
> a figure down or just use whole numbers?
>
> Many thanks in advance,
> Simon


0
twogilvy (1078)
9/22/2003 5:42:02 PM
Simon,

Three options,

Quotient gives the integer portion of a division
=QUOTIENT(A1,B1)
The remainder can be returned using
=MOD(A1,B1)
In the above case A1 is the numerator, B1 is the
denominator

Int also returns the integer portion of a number
=INT(A1/B1)

Rounddown rounds down
=ROUNDDOWN(A1/B1,0)

Dan E

"Simon Argent" <Simon.Argent@warnerbros.com> wrote in message news:05c001c3812d$cb7a57d0$a101280a@phx.gbl...
> Hi,
>
> I am currently having a problem with decimal places.
> Basically I don't want any whatsoever.
>
> In a cell, I am doing division between 2 figures to find
> out how many items I can purchase with an amount of money.
>
> So, the formula is
> AmountOfMoney/CostOfItem
>
> If the ansewr was for example 63.97 then the results cell
> would show 64 (I have it set so that 0 decimals are shown)
>
> There are then other calculations throughout the
> spreadsheet that use this result. However, since I can
> only afford 63 WHOLE items, I don't want the 0.97 to be
> included as part of the equation since this throws out all
> of my figures.
>
> Is there any possible way that I can force excel to round
> a figure down or just use whole numbers?
>
> Many thanks in advance,
> Simon


0
9/22/2003 5:43:28 PM
Many thanks to both of you. I have managed to get the 
RoundDown function working and now all my figures are 
perfect :-)
0
9/22/2003 6:09:51 PM
Reply:

Similar Artilces:

SOP NonInventory Unit Cost 6 Decimal Places
Is there any simple way to increase the currency decimal places to greater than the current highest value of 5? Thanx Scott, I don't believe there is any easy or even moderately difficult way to do this, as the field types used to store amounts in GP only go up to 5 decimal places. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Scott Rutledge" <sersoar@hotmail.com> wrote in message news:E102B81B-8555-400E-843C-240C58794294@microsoft.com... > Is there any...

Reduce currency decimals
Hi, Is there any tool which will reduce the currency decimals on our Great Plains system. We want to reduce our system currency decimals. Thank you. KT I'm not sure if this is what you mean, but there's Tools >> Utilities >> Inventory >> Change Decimal Places This utility can change the decimal places for items. I don't know of any system-wide change for decimal places. Eleni "KT" wrote: > Hi, > > Is there any tool which will reduce the currency decimals on our Great > Plains system. We want to reduce our system currency decimals....

decimal problem
Hi all I a very strange effect when using different decimal settings and calculating some values: DECLARE @Decimal1 decimal(5, 2) DECLARE @Decimal2 decimal(30, 2) SET @Decimal1 = 0.2 SET @Decimal2 = 0.2 SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal1)) SELECT (CONVERT(decimal(15, 15), 0.000000000322900) * (1 - @Decimal2)) The first result is correct (0.00000000025832000), the second is wrong, why ??? Thanks for any comments Best regards Frank Uray Hi Frank This is what happens when you multiple these precision&scales Precision 15 + 3...

Decimal
In a laboratory test we do the temperature has to be reported to the nearest 0.5�C. Can anyone let me know how to format it so that when the result is a whole number, it leaves the decimal point out? eg 22�C or 22.5�C (not 22.�C or 22.0�C) Alan "Alan Cocks" <alandrob@netspace.net.au> wrote in message news:btlqhd$19fi$1@otis.netspace.net.au... > In a laboratory test we do the temperature has to be reported to the > nearest 0.5�C. > Can anyone let me know how to format it so that when the result is a whole > number, it leaves the decimal point out? &g...

VBA code and decimals
I have some VBA code as this: rrows=lastrow/4 if lastrow is 138 then rrows should be 34.5 but I always get 34(no decimal) How do I fix that Thanks I just did this and got 34.5 Sub lastrowdecimal() lastrow = 138 rrows = lastrow / 4 MsgBox rrows End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "pcor" <pcor@discussions.microsoft.com> wrote in message news:F21838BC-42AA-48A3-9B0C-D2139287A187@microsoft.com... >I have some VBA code as this: > rrows=lastrow/4 > > if lastrow is 138 then rrows should be 34.5 but I always get 34(no > decimal) >...

Currency and the number of decimal places
I have an application which has neen running for a number of years, firstly on Access2000 then XP and now on Access 2003. It uses many currency fields for invoicing , credit notes etc. Each field which is of currency type has the decimal property set to 2. Up until recently the ATB balanced fine but lately it has been a few cents out. The reason is that some amounts have been put in with 3 decimal places and some calculations (eg GST of 10% of $398.68 returning to the GST field a value of $39.868) returning 3 decimal places. I guess with the calculations, I can code to make sure only ...

No decimals?
Hello! Is it possible to stop Money (2005) from using decimal places in the base currency? I am using a currency that uses only integers and no cents (because cents are almost worthless), and invoices amounts usually get some cents due to sales tax calculations, Is it possible to get rid of them? I find this very annoying since cents accumulate over several transactions, and end up with an slightly unreal balance... Thanks! Keiv Hi, It was possible in older version (pobably until 2001). You had to change the Windows's regional settings in the control panel before a new file creatio...

Decimal number to percentage formula (newbie question)
I've got a column full of numbers like 0.5, 1.0, etc. I need them to look like 0.500%. I figured out how to format the cells so that 0.5 looks like 0.500, but when I change the cell format to percentage, it makes 0.500 into 50.000%. So I figured out how to make a formula that divides 0.500 by 100 (=R1C6/100), resulting in 0.005, which I can format to a percentage and get 0.500%. Where I'm stumped is in applying that formula to the entire column. Instead of =R1C6/100, I want my formula to say =all rowsC6/100. How do I tell it {allrows}? Thanks for putting up with a newbie Ken Benson ...

Cheapest place to buy Publisher 2002 in the UK please?
Can anyone tell me where to buy Publisher 2002 (not 2003) in the UK without paying around �100? Or does anyone have an unwanted copy I could purchase. Thanks Chris Have you tried ebay? "chris" <cris.ebbs@nospambtopenworld.com> wrote in message news:bnjgkr$eht$1@sparta.btinternet.com... > Can anyone tell me where to buy Publisher 2002 (not 2003) in the UK without > paying around �100? > Or does anyone have an unwanted copy I could purchase. > Thanks Chris > > ...

How to place an annotation arrow on the line in a chart?
is that possible in excel 2007 to place an annotation arrow, on the plotted line, in a chart? Hi, This approach should still work in xl2007. http://www.andypope.info/ngs/ng47.htm Cheers Andy Need Help on axis problem in a chart wrote: > is that possible in excel 2007 to place an annotation arrow, on the plotted > line, in a chart? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Don't lose my decimals!
I have a int field and a decimal field (which however contains only numeric values) I need to divide this fields: SELECT field1/CONVERT(INT,field2) The operation is Ok, but I don't get decimal. I tried even SELECT CONVERT(FLOAT,field1/CONVERT(INT,field2)) but it's the same. How do I get decimals? And I want to get only two. Thanks. Luciano "Luciano (DOC)" <lucianodoc@luciano.doc> ha scritto nel messaggio news:4b9a83b3$0$1110$4fafbaef@reader2.news.tin.it... >I have a int field and a decimal field I mean a varchar field. L. Data typ...

Placing Icons
Hi I'm facing a problem from last month but I'm not able to put Icons whether it can be done through conditional formatting or through a macro. If somebody can help as its really urgent for me !!!!! I have values for two months - Current and Previous. If current month's value is closer to zero than previous month's value then i want to show a green arrow. If current month's value is away from zero than previous month's value then i want to show a red arrow. Though these values are positive and negative both, i need to apply conditional formatting on ...

European vs American decimals
I have transferred a chart with statistics from an American Website to my Dutch version of Excel 2003. All the statistics are stated in the American format (with a period as the decimal sign): example = 8.6 I am not able to make formulas because Excel doesn't seem to recognize the period (.) as a decimal sign. (all my equations get the answer 0) I would like to know how to change the period into a comma(the Dutch decimal sign), so that I can use the formula function. or I would like to know how to make Excel recognize the Amerian format to use the formula function. Thank you H...

Count digits before decimals and after decimals
Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please? Hi, Why doesn't LEN help. try these =LEN(INT(A1)) and for the decimal portion =IF(ISERROR(FIND(".",A1)),0,LEN(A1)-FIND(".",A1)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while...

decimal separator?
Hi, I'm using web quiries to obtain data from web pages. Some of the sites use ',' as a desimal separator others use '.'. I'm from Finland so I'd like to use ',' as decimal separator. Is there a way to format those U.S.-format('.') numbers to Finnish-format(',') without changing the setting for the whole progmam/operating system?? Thank you, Jimmy format the cells where you will paste as text paste in the data format the cells a general do edit=>Replace Replace what . replace with , -- Regards, Tom Ogilvy Jimmy <jj_22_jj@yahoo...

Number Format
I have a system that records attribute data. An Engineer enters a specification range for the values and an integer value for the desired number of decimal places to display. I have the following formula in my report's record source Round([Act_Value], Nz([Dec_Place],0)) This works to limit the number of decimals, but doesn't "set" the display. For example [Act_value] = 0.5 [Dec_place] = 3 The above formula will diplay 0.5. I want it to display 05.00. Is there a way to do this? Any help will be appreciated. Use the format function Format(Round([Act_Value],...

Qty Decimals
Hi Out There, Does anyone know if there is a way to set a part to 4 decimals, but to show 0 decimal places on the PO & SOP Screens and printouts? We would like to have the option of 4 decimals for Bills of Materials, but we don't want the orders and PO's to show 4.0000 because it forces the data entry to add a perios in order to make it a whole number. (We've been having issues with that) Thank you! You can do this with Modifier/VBA. If you don't find a better way and want to pursue this route let me know and I will help. ...

format and decimals in control text
Hi format and decimals in a text box do not seem to work as expected. I can set format to general number or leave it blank and decimals to 0 or 1, makes no difference, I get 1 decimal in some text boxes and no decimals in others. the decimals are not rounded off either, they can be .3 or .9. does anyone know why and how to deal with this problem? regards LP wrote: >format and decimals in a text box do not seem to work as expected. >I can set format to general number or leave it blank and decimals to 0 or 1, >makes no difference, I get 1 decimal in some text boxes and no decimals...

Degrees Minutes Decimal Minutes to Decimal Degrees.
Hi All, I have got a worksheet with cells like: E174 36.992 I need to get that into decimal degrees like E174.61653 Is there a way to do this with a forumla? I start with find, left, right functions, but I ended up parenthesis hell. Cheers -Al "Al" <bigal.nz@gmail.com> wrote: > I have got a worksheet with cells like: > E174 36.992 > I need to get that into decimal degrees like > E174.61653 One way: =LEFT(A1,FIND(" ",A1)-1) & TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1))/60,".00000") ----- original messa...

how do I place a clip from the note book to a web site page?
How do I copy/transfer/ ETC a clip from the notebook to a web site building software page? also how do I move a clip to any document? I ,think that is the quesiton. Can't you right-click the clip in OneNote, copy it, then paste that into your "web site building software page"? What program are you using to build the website? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Demi M...

Ranking 1st
i want to know if there is anyway that i can put a number next to m column that works out each time the average changes. for example: i have the name in one column the average in the next then i want a number in the next that says what place he/she is. John 5.7 3rd Emma 6.3 2nd Mark 9.2 1st Sarah 1.4 4th is this possible ?? Many Thanks Ben -- Message posted from http://www.ExcelForum.com Hi Benn if you values are stored in column B enter the following in C1 =RANK(B1,$B$1:$B$10) and copy this formula down -- Regards Frank Kabel Frankfurt, Germany > i w...

number of quantity decimals
Hello: Other than perhaps rounding issues, are there any problems with having quantity decimals for some items in inventory being 5, some being 4, and some being 3? childofthe1980s If it works for your parts and pricing, it works for GP. I frequently have some parts with 5 decimals and others with 2 -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "childofthe1980s" wrote: > Hello: > > Other than perhaps rounding issue...

Decimals
Hi , I have a pedantic client who when he exports the report to excel does not want to see two decimal places . I have set it to N0 and P0 , but alas no luck , any suggestions ? Regards Malcolm ...

Place a form control value in Access to a specific cell in Excel.
I didn't get a response on my last post, so I'm hoping someone will have an idea on this request. Below is my original post: Hi, All! I'm looking for some help in moving data from my Access form to specific cells in an Excel template. I'd like to create a command button on my Access form that will open the .xlt file and transfer some of the values on my form and plug them into the .xlt. For example, I have the cost of equipment calculated on my form control (3 printers @ $300 each = $900), the command button will capture that value and plug it into the capital expenditure ...

My network places hangs Explorer
Windows XP sp3 Windows Explorer works fine. But when I click on My network places, Explorer hangs and and Windows diagnostic and reporting window comes up. This is followed by the Dr Watson programme hanging. After going to Task manager and stopping Explorer, Explorer restarts and works as above. The network connection (Opera, Firefox, IE) is working fine. Has XP become corrupted and can it be fixed? ...