How to round up in excel and/or access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

NSKL

Thread Starter
Joined
Apr 11, 2004
Messages
3
I'm trying to create a database in Microsoft Access and I have a calculated price that I want to round up to the nearest $.09. I'm not sure how or if this can be done. If Access will not allow this kind of calculation perhaps someone can tell me how to do it in Excel.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Welcome to TSG!

If you're always rounding up to the nearest 9 cents, then just round down to the nearest 10 cents and add 9 cents, like so:

=rounddown(a1,1)+.09

Round functions don't exist in Access like they do in Excel, so it's easier to do in Excel.
 
Joined
Aug 30, 2003
Messages
2,702
Oops/ouch - that one's A PIG. Anne's crashed on some tests, mine crashed on others.

This one's passed all tests so far, to 3 DPs bar x.x9x ;

=IF(CEILING(A1,0.1)-A1=0.05,A1+0.04,ROUNDDOWN(A1,1)+0.09)

If you can make it crash, holler.

Rgds/Happy Easter,
Andy
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
LOL, Andy. I CHANGED mine (from round to rounddown)....why don't it work for ya?
 
Joined
Aug 30, 2003
Messages
2,702
>> why don't it work for ya?

x.x5x ; I expect that's why you changed it.

Rgds/Happy Easter,
Andy
 

NSKL

Thread Starter
Joined
Apr 11, 2004
Messages
3
To Dreamboat - the formula for Excel, =round(a1,1)+.09, worked great. Thanks a lot.

To XL Guru - I tried the formula/function starting +IF(CEILING, but it didn't work for me. I'm probably not entering it correctly. I tried to do it as a function and assumed that the A1 reference is the field that I'm trying to round to the next $.09. Any clue as to what I'm doing wrong?

Norm
 
Joined
Aug 30, 2003
Messages
2,702
>> =round(a1,1)+.09

I believe this is what Anne posted originally. It doesn't work in some cases e.g. 0.45 goes to 0.59, whereas I believe you'd want it to go to 0.49.

That said, I don't know why you're quoting it since IIRC Anne updated as below ;

=rounddown(a1,1)+.09

This works fine AFAIK, and in view of it's brevity makes my =IF(CEILING ... redundant.

The =IF(CEILING ... works for me, notwithstanding it's academic.

Rgds,
Andy
 

NSKL

Thread Starter
Joined
Apr 11, 2004
Messages
3
To Dreamboat - I was entering the excel formula =round(A1,1) +.09 in a number of fields and discovered that I got the right answer with any numbers that ended in 0 - 4, but they jumped an additional $.10 with any that ended in 5 - 9. So I fooled around and tried changing it to =rounddown(A1,1) +.09 and it seems to work correctly for all of them. I don't know why. Just thought you'd like to know. Thanks again.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top