Rounding Value Up to Whole Number

I have a report footer that I'm adding values from the detail section.  Many 
of my values that I'm adding are .333.  Once I sum the value in the footer, I 
end up with not a whole number, for example 9.999.  Is there a property I can 
set to bring the value to the next whole number.  Thanks for your help
0
Utf
3/7/2008 3:23:02 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
678 Views

Similar Articles

[PageSpeed] 23


Try
=- Int( - [MyField])

(note there is a - before the Int and before the my field)

Substitute the name of your own text box)

Evi


int(number*100 + 0.5) / 100
"PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
news:CBDE76F7-8837-4223-BF5C-E8A4F670AE9B@microsoft.com...
> I have a report footer that I'm adding values from the detail section.
Many
> of my values that I'm adding are .333.  Once I sum the value in the
footer, I
> end up with not a whole number, for example 9.999.  Is there a property I
can
> set to bring the value to the next whole number.  Thanks for your help


0
Evi
3/7/2008 3:47:33 PM
Where do I put this, in the format control?  I tried it there and it didn't 
change the number to a whole number.  I appreciate your help, thank you

"Evi" wrote:

> 
> 
> Try
> =- Int( - [MyField])
> 
> (note there is a - before the Int and before the my field)
> 
> Substitute the name of your own text box)
> 
> Evi
> 
> 
> int(number*100 + 0.5) / 100
> "PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
> news:CBDE76F7-8837-4223-BF5C-E8A4F670AE9B@microsoft.com...
> > I have a report footer that I'm adding values from the detail section.
> Many
> > of my values that I'm adding are .333.  Once I sum the value in the
> footer, I
> > end up with not a whole number, for example 9.999.  Is there a property I
> can
> > set to bring the value to the next whole number.  Thanks for your help
> 
> 
> 
0
Utf
3/7/2008 4:38:02 PM
Combine it with your Sum field so that the text box with which you added up
your field now says

eg

= -Int(-Sum([YourField]))

Replace 'Your field' with the name of the field which you are summing.

Evi

"PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
news:9D5F1CEA-D598-4B8E-A045-F06234058CBB@microsoft.com...
> Where do I put this, in the format control?  I tried it there and it
didn't
> change the number to a whole number.  I appreciate your help, thank you
>
> "Evi" wrote:
>
> >
> >
> > Try
> > =- Int( - [MyField])
> >
> > (note there is a - before the Int and before the my field)
> >
> > Substitute the name of your own text box)
> >
> > Evi
> >
> >
> > int(number*100 + 0.5) / 100
> > "PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
> > news:CBDE76F7-8837-4223-BF5C-E8A4F670AE9B@microsoft.com...
> > > I have a report footer that I'm adding values from the detail section.
> > Many
> > > of my values that I'm adding are .333.  Once I sum the value in the
> > footer, I
> > > end up with not a whole number, for example 9.999.  Is there a
property I
> > can
> > > set to bring the value to the next whole number.  Thanks for your help
> >
> >
> >


0
Evi
3/7/2008 6:15:14 PM
Worked like a charm, thanks so much

"Evi" wrote:

> Combine it with your Sum field so that the text box with which you added up
> your field now says
> 
> eg
> 
> = -Int(-Sum([YourField]))
> 
> Replace 'Your field' with the name of the field which you are summing.
> 
> Evi
> 
> "PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
> news:9D5F1CEA-D598-4B8E-A045-F06234058CBB@microsoft.com...
> > Where do I put this, in the format control?  I tried it there and it
> didn't
> > change the number to a whole number.  I appreciate your help, thank you
> >
> > "Evi" wrote:
> >
> > >
> > >
> > > Try
> > > =- Int( - [MyField])
> > >
> > > (note there is a - before the Int and before the my field)
> > >
> > > Substitute the name of your own text box)
> > >
> > > Evi
> > >
> > >
> > > int(number*100 + 0.5) / 100
> > > "PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message
> > > news:CBDE76F7-8837-4223-BF5C-E8A4F670AE9B@microsoft.com...
> > > > I have a report footer that I'm adding values from the detail section.
> > > Many
> > > > of my values that I'm adding are .333.  Once I sum the value in the
> > > footer, I
> > > > end up with not a whole number, for example 9.999.  Is there a
> property I
> > > can
> > > > set to bring the value to the next whole number.  Thanks for your help
> > >
> > >
> > >
> 
> 
> 
0
Utf
3/7/2008 7:05:01 PM
Reply:

Similar Artilces:

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

I need to round up to the nearest 50.
I am trying to calculate the number of raw material bags I need in a batch. If I use the mround function is will round down. Is there a way to only round up? =ROUNDUP(A21/50,0)*50 or =CEILING(A21,50) -- HTH Bob Phillips "karenm" <karenm@discussions.microsoft.com> wrote in message news:DBE54F5E-0524-4DFF-BC52-D966BBEE26E9@microsoft.com... > I am trying to calculate the number of raw material bags I need in a batch. > If I use the mround function is will round down. Is there a way to only > round up? > Hi karenm, First of all, I believe MROUND rounds...

Receivings Number
Hi, When I am doing my purchase receiving at the Receivings Entry, I sometimes encounter some problem which the Receivings Number. For example, suppose the new receivings number should be REC/03/000004, but this number REC/03/000005 is given to me instead. What could be the possible cause of the missing receivings number:REC/03/000004. (this no. is not used by anyone). Please advise or tell me how can I avoid. Thanks in advance. I believe it's when someone started to do a receivings but then deleted it. It already increments to the next number. You can prevent deleting of documen...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

Limit number of characters in a cell
Hi, Is there a way to limit the number of characters that a user can key into a cell. I want to get a list of names and addresses imputted by user, but I don't want them to be more than 35 characters. Thanks for the help Dr. Senji Take a look at Data|Validation. You can have excel yell at the user when they hit enter after typing in a too-long string. Dr Senji wrote: > > Hi, > > Is there a way to limit the number of characters that a user can key into a > cell. > > I want to get a list of names and addresses imputted by user, but I don't > want the...

Storing distinct values in an array
Hello .. in the speadsheet, i have a column containing a series of numbers maybe of them repeated multiple times... i would like to store all distinct values in an array any ideas on how i could do that ... eg , if these were the numbers going down column then 1 3 5 7 5 3 5 7 5 4 3 4 5 7 8 5 3 .. store 1,3,5,7,9,4,8 in an array, in no specifi order thank you shimee -- shimee ----------------------------------------------------------------------- shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showt...

Counting the number of times more than 1 variable occurs
I need to determine how many Separations were processed by a particular salesperson. A | B 1 MIKE | SEPARATION 2 MIKE | LEAVE 3 SARA | SEPARATION 4 JOE | SEPARATION 5 JAMIE | LEAVE 6 MIKE | LEAVE 7 JOE | LEAVE 8 SARA | SEPARATION The only way I can think of is the COUNTIF Funtion *COUNTIF(-range,criteria-)* =COUNTIF(A1:A8,(A1:A8="MIKE")*(B1:B8="SEPARATION")) This does not work though. The results return 0 with no errors when the answer should be 1. does anyone else know of a wa...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

number of lines on a word document
Operating System: Mac OS X 10.5 (Leopard) I want to produce a long, double-spaced document with the same number of lines on each page, not one that flips to a new page if the end paragraph is too large to fit. Can anyone help? Maintaining a constant number of lines per page is dependent on at least 2 factors: 1- Using the same font & font size throughout, & 2- Turning off Widow/Orphan control in Format> Paragraph - Line & Page Breaks. Due to the proportional spacing considerations of modern word processing -- which is more closely akin to typesetting than typewr...

Counting number of words in a sentence and highlighting or commenting
Hi, I found some old code, from Helmut of Bavaria I believe, that is supposed to find long sentences in a document: Sub test002() Dim rDcm As Range ' the documents main story range Dim oWrd As Object ' a word Dim oSnt As Object ' a sentence Dim oPrg As Paragraph ' a paragraph Dim lWrd As Long ' a counter for words Dim lSnt As Long ' a counter for sentences Dim lPrg As Long ' a counter for paragraphs Set rDcm = ActiveDocument.Range For Each oPrg In rDcm.Paragraphs lPrg = lPrg + 1 lSnt = 0 For Each oSnt I...