Round Excel values up to nearest $5.00

What formual would I enter to round an Excel value up to the next whole 
dollar amount in $5.00 increments?  For example, any amount between $30.01 
and $34.99 would be rounded and displayed as $35.00.
0
USAOz (3)
10/10/2004 11:23:09 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
770 Views

Similar Articles

[PageSpeed] 15

Hi
try:
=ROUND(A1/5,0)*5

Also see:
http://www.xldynamic.com/source/xld.Rounding.html


-- 
Regards
Frank Kabel
Frankfurt, Germany


USAOz wrote:
> What formual would I enter to round an Excel value up to the next
> whole dollar amount in $5.00 increments?  For example, any amount
> between $30.01 and $34.99 would be rounded and displayed as $35.00.
0
frank.kabel (11126)
10/10/2004 11:36:58 AM
One way:

=CEILING(A1,5)
or
=ROUNDUP(A1/5,0)*5


USAOz wrote:
> 
> What formual would I enter to round an Excel value up to the next whole
> dollar amount in $5.00 increments?  For example, any amount between $30.01
> and $34.99 would be rounded and displayed as $35.00.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/10/2004 11:56:16 AM
If you really mean round up, try

=CEILING(A1,5)

-- 

HTH

RP

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OEyKZ1rrEHA.1988@TK2MSFTNGP09.phx.gbl...
> Hi
> try:
> =ROUND(A1/5,0)*5
>
> Also see:
> http://www.xldynamic.com/source/xld.Rounding.html
>
>
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> USAOz wrote:
> > What formual would I enter to round an Excel value up to the next
> > whole dollar amount in $5.00 increments?  For example, any amount
> > between $30.01 and $34.99 would be rounded and displayed as $35.00.


0
bob.phillips1 (6510)
10/10/2004 2:09:24 PM
You can use the INTfunction to round up or down as follows:

=INT((A1+2.5)/5)*5

The 2.5 is half of the multiple to which you are rounding, and the 5s are 
what you are rounding to.

--Dave Pettit
Flint MI


"USAOz" wrote:

> What formual would I enter to round an Excel value up to the next whole 
> dollar amount in $5.00 increments?  For example, any amount between $30.01 
> and $34.99 would be rounded and displayed as $35.00.
0
10/10/2004 3:07:02 PM
Hi!

Thank you VERY much for your correct and speedy response! Also, thanks for 
the tip on that useful website link - very interesting!


"Frank Kabel" wrote:

> Hi
> try:
> =ROUND(A1/5,0)*5
> 
> Also see:
> http://www.xldynamic.com/source/xld.Rounding.html
> 
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> 
> USAOz wrote:
> > What formual would I enter to round an Excel value up to the next
> > whole dollar amount in $5.00 increments?  For example, any amount
> > between $30.01 and $34.99 would be rounded and displayed as $35.00.
> 
0
USAOz (3)
10/10/2004 7:41:04 PM
Hi Dave!

Thanks for that response - it is closer to what I was seeking than the other 
2 (also correct) responses.  BTW, thanks for your explanation - that was 
something I never understood before and had I known that, probably would not 
have needed to post the question in the first place!  Thanks again!

"Dave Pettit" wrote:

> You can use the INTfunction to round up or down as follows:
> 
> =INT((A1+2.5)/5)*5
> 
> The 2.5 is half of the multiple to which you are rounding, and the 5s are 
> what you are rounding to.
> 
> --Dave Pettit
> Flint MI
> 
> 
> "USAOz" wrote:
> 
> > What formual would I enter to round an Excel value up to the next whole 
> > dollar amount in $5.00 increments?  For example, any amount between $30.01 
> > and $34.99 would be rounded and displayed as $35.00.
0
USAOz (3)
10/10/2004 7:47:04 PM
Can you explain two things to me.

First, how does this solution meet you specified requirement of '...round an
Excel value up to the next whole
dollar amount in $5.00 increments?  For example, any amount between $30.01
and $34.99 would be rounded and displayed as $35.00'. By my calculations,
this formula will return 30 for a value of 30.01 not the 35 you stated.

Secondly, in what way is it any different to Frank's, let alone closer?

-- 

HTH

RP

"USAOz" <USAOz@discussions.microsoft.com> wrote in message
news:1A4D81B2-6FD6-4D79-94B0-1993C4D26D08@microsoft.com...
> Hi Dave!
>
> Thanks for that response - it is closer to what I was seeking than the
other
> 2 (also correct) responses.  BTW, thanks for your explanation - that was
> something I never understood before and had I known that, probably would
not
> have needed to post the question in the first place!  Thanks again!
>
> "Dave Pettit" wrote:
>
> > You can use the INTfunction to round up or down as follows:
> >
> > =INT((A1+2.5)/5)*5
> >
> > The 2.5 is half of the multiple to which you are rounding, and the 5s
are
> > what you are rounding to.
> >
> > --Dave Pettit
> > Flint MI
> >
> >
> > "USAOz" wrote:
> >
> > > What formual would I enter to round an Excel value up to the next
whole
> > > dollar amount in $5.00 increments?  For example, any amount between
$30.01
> > > and $34.99 would be rounded and displayed as $35.00.


0
bob.phillips1 (6510)
10/10/2004 10:52:29 PM
Explain not only those two things, what is meant by this response being
"closer" than the 2 other "correct" responses.

There should be only one answer. If the other two are correct, this one gives
either an identical result or something different. It it's different, and the
other two are "correct", then this one is wrong, not "closer".

OTOH, if this one is "closer", the other two aren't correct, are they?


On Sun, 10 Oct 2004 23:52:29 +0100, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>Can you explain two things to me.
>
>First, how does this solution meet you specified requirement of '...round an
>Excel value up to the next whole
>dollar amount in $5.00 increments?  For example, any amount between $30.01
>and $34.99 would be rounded and displayed as $35.00'. By my calculations,
>this formula will return 30 for a value of 30.01 not the 35 you stated.
>
>Secondly, in what way is it any different to Frank's, let alone closer?
>
>-- 
>
>HTH
>
>RP
>
>"USAOz" <USAOz@discussions.microsoft.com> wrote in message
>news:1A4D81B2-6FD6-4D79-94B0-1993C4D26D08@microsoft.com...
>> Hi Dave!
>>
>> Thanks for that response - it is closer to what I was seeking than the
>other
>> 2 (also correct) responses.  BTW, thanks for your explanation - that was
>> something I never understood before and had I known that, probably would
>not
>> have needed to post the question in the first place!  Thanks again!
>>

0
anonymous (74722)
10/10/2004 11:22:25 PM
Reply:

Similar Artilces:

Pasting data from Excel
Hello everyone, I'm not sure if I should be posting this question here or in the Excel forum but here goes. Is it possible to copy data from multiple cells in Excel and then paste them into multiple lines of the criteria section of an Access query? For example, Given cells and values: A1- 1 A2- 2 A3- 3 I would like to be able to copy this data from Excel and paste it into an Access query like : Criteria: 1 or: 2 3 I am using Access 2002 SP3 and Exc...

Excel Drop Down Box
I'm trying to edit an excel worksheet that has drop down boxes. However, the drop down boxes are not typical forms. These drop dow boxes appear to be normal cells (They contain text). When I click o the box, a little gray box shows up w/ a down arrow to the right of th cell. However, if you right click on the cell, there aren't an property options that are displayed. I was wondering if anybody had any idea what kind of drop down box thi is. How can I edit or create one -- Message posted from http://www.ExcelForum.com It sounds like it's under Data|Validation. chris313 wr...

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...

assign numeric value to letters and sum with other numbers
I apologize if I am duplicating an earlier question, but I can't find the answer. How do I sum a row or column that has numbers and letters by giving the letters a numerical equivalent? -- WJG On Mon, 11 Jan 2010 12:19:01 -0800, Galadad <Galadad@discussions.microsoft.com> wrote: >I apologize if I am duplicating an earlier question, but I can't find the >answer. How do I sum a row or column that has numbers and letters by giving >the letters a numerical equivalent? Could you give an example of input and expected output. Lars-�ke Just guessing...

UPC Equation #5
Okay awesome. Thanks so much for the help guys. I went with some hidde cells to insert the extra parts of the formula. Now, is there a way t make it apply to each and every field in Column A? and Column B? Thanks -- PneumaG ----------------------------------------------------------------------- PneumaGT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showthread.php?threadid=27326 You didn't mention exactly which formulas you went with, but just click on the fill handle, and drag down to copy as far as neede...

excel margin issues on landscape
When I print a spreadsheet I cant get it to print to the full page - it prints smaller unlike older excel program. Also when i set the margins for a spreadsheet the left hand margin wont move over to the edge of page like right hand side? In Page Setup: If you are using the Scaling option to print to a certain number of pages wide by pages tall and/or you are using the columns to repeat at left, try: - clearing the number of pages tall value (so that it is blank), and/or - if you are printing to one page wide, remove the columns to repeat at left Simon "Peter MB" wrote: >...

Excel macro list
In Excel 2003 I used to be able to list all macros in a workbook by pressing Alt+F8. Now all I get is a series of ribbon help letters... What's changed? Is there still a way of accessing macros via Alt+F8? Any suggestions appreciated. Hi, ALT+F8 works for me in E2007. What do you mean by 'I get is a series of ribbon help letters' Mike "pbaker6" wrote: > In Excel 2003 I used to be able to list all macros in a workbook by pressing > Alt+F8. Now all I get is a series of ribbon help letters... What's changed? > Is there still a way of...

Can I only allow printing to pdf in Excel?
I have created a template in Excel which has been set up so that the layout is perfect when printing to pdf (which is how the document will mostly be used) but the layout changes if printing direct to our printer. Is there a way that I can ONLY allow printing to pdf from this document? Hello You may consider using some VBA to achieve this. One way is to use the Workbook_BeforePrint event and specify the pdf printer in the PrintOut method, eg: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PrintOut copies:=1, ActivePrinter:="CutePDF Writer on CPW2:" End Sub Pl...

How to make a column of formulas all ROUND
I created a spreadsheet in which I have a column of formulas. Most of these fomulas are simply pulling a single number off another sheet. I want to make all the formulas ROUND versions of the existing formula without having to go into each cell and making the change. They are not in order to which I can just make the first fomula a ROUND fomula and copy down. So, is there a way to select a range of cells and make the existing fomulas all ROUND versions? Thanks. Would this help? Sub RoundAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If...

550 5.7.1 relay error messae
I have select number of Clients (3) out of 27 who we can not send emails to, they are in a separate domain from ours. Your message did not reach some or all of the intended recipients. Subject: test 4:35pm Sent: 2/27/2007 4:34 PM The following recipient(s) could not be reached: Sean ***** on 2/27/2007 4:34 PM You do not have permission to send to this recipient. For assistance, contact your system administrator. <mail.n*******.org #5.7.1 smtp;550 5.7.1 Unable to relay for tsc16.****.****@***.local> Rhodes.messiah@gmail.com wrote in news:1172615546.953350.313930 @j...

query will not write to excel
I have set up a query to a Foxpro .dbf file in a database from excel. When I tell Excel to import the data it it appears to be working but never seems to return the data. Running the same query via msquery.exe returns the data with no problem. Can anyone tell me what the problem is ? ...

Converting Numerical values to Words
I am fairly new to the ins and outs of Microsoft Access 2003 but have been able to work through all of my problems thus far except one. I started using check boxes for storing user inputed data from a form. When the box is checked I have made it equal a value ranging from 1 to 6 according to the desired group. This stores the number in a table which I then reference many times in queries as well as reports. Here is my question, how do I convert from those stored numbers, taken from the check boxes, to words that can be easily outputted to a report so that anyone can read it without ...

EXCEL FORMULA #28
Good afternoon, I'm trying to fine a formula which would show me how much money I would save on a mortgage if I were to pay additional principal each month--in addition to paying the additional principal how long would it take to pay off. I'm looking at a 160k mortgage at 7.5 for 30 years. I'll like to pay this off as soon as possible by paying additional principal each month. There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Kam1999i wrote: > > Good afternoon, > > I'm ...

OLE: Excel.Application
Hello, in VB.Net, I use Excel to display results : dim xl as new Excel.Application // creates an Excel process // snip (putting values into cells) xl.Visible = true If the user closes the Excel file and then my program, the Excel process is killed in memory, which is good. If the user closes my program first and then the Excel file, the Excel process remains in memory ! How can I make sure the process will be killed ? Thanks ! Hi You need to set xl.quit (and before that ensure that excel doesn't halt and ask things like "save changes?" on quitting) somewhere in your p...

Export relationship information from Visio to Excel
Hello all, Is there a way to export information regarding relationships from a visio diagram to an excel spreadsheet? In addition, is there a way to tell the excel spreadsheet to eliminate or change a relationship and for that action to be applied onto the visio diagram? thanks, ivan as a general answer I'd have to say "no, not without custom code". You didn't define what you meant as a relationship. al "Ivan Salas" <IvanSalas@discussions.microsoft.com> wrote in message news:6332A594-E4AF-4E8B-BA2D-7A4BC17962B3@microsoft.com... > Hello all, > &...

startup excel euro symbol
when i digit € symbol inside any application excel 2007 automacic startup and for me is impossible to use this symbol anywhere, i use windows xp professional ..have you a response to solve this problem? thanks ...

Excel 2003 Print Issue
I have created a spreadsheet to help with a university engineering assignment and I have added a worksheet that is basically an automatically generated report of all the calculations. I have set the Print Area up in such a way so that the results are printed out in well defined pages (e.g. page 1: title page, page 2: summary of input variables, page 3: summary of calculation results etc). The report is arranged vertically in the worksheet, so the pages are 'stacked' on top of each other. It prints out fine in Excel 2000 and 2002 but I recently upgraded to Excel 2003 and now find tha...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Excel Graphing Line References off when chart is a sheet.
I have noticed that when any graph is created in EXCEL and you hover you mouse over the dataline you receive that corect response. If you convert the chart to a sheet, the hover of the data line is now not representative of the the y axis directly below it. The data being graphed is correct now the hover represents the "series" (x-Axis) correctly but does not represent the "Point" (y-axis) correctly at all. Tne Y-axis datapoint reference is wrong. Any help? ...

Exchange 2003 OWA #5
I accidentally killed my default website that contained the exchange virtual directories. Can someone tell me how to recreate them? Thanks ...

Editable Excel Spreadsheet Online?
Hi, I tried to recent find information on this, but could find very little. How difficult would it be to host an excel spreadsheet online where visitors to the site can directly view and edit it? Right now, I can upload the spreadsheet to our web site and visitors can view it, but if they edit it, they can only save it to their local drive. I would like the users to be able to save the copy on the server. What would be involved in something like this? I assume for starters (if it's do-able) we'd need Windows hosting (we're not hosting ourselves) and some ASP support. Any de...

how do I delete documents from the start list in word and excel?
how do I delete documents from the start list in word and excel? You can not clear it whenever you want. You can however set the no of file names to be displayed to 0 which clears the list... In Excel 2003 Tool->Options->General Enter 0 against 'Recently Used File List' of clear the check box. Click 'OK' Word has a similar option. For 2007 versions or if you want to play with Registry Settings (not advised unless you understand it well) see http://www.mydigitallife.info/2008/01/13/how-to-clear-and-delete-recent-documents-list-in-office-2007-word-excel-p...

Error saving Excel files in a network drive
I have a problem saving Excel files onto a network drive. I get an error saying it was imposible to save the file. It creates a temporary file and then I have to open it and save it as a new document. This issue doesn�t occur saving the file in my hard disk. This happens with "Full control" access to the shared folder... I have Windows XP and Office 2000. Thanks in advance Mateo. Hi Mateo, > I have a problem saving Excel files onto a network drive. I get an error > saying it was imposible to save the file. It creates a temporary file and > then I have to open it and sav...

Import contacts from Excel into Outlook Contacts
I know this can be done from the import function in Outlook but how does one control where each field goes? There's a Map Fields step in the wizard. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Maurice" <anonymous@discussions.microsoft.com> wrote in message news:5e0a01c48154$f16ff9e0$a401280a@phx.gbl... > I know this can be done from the import function in > Outlook but how does one control where each field goes? ...