# How to round up in excel and/or access

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### NSKL

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
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.

#### XL Guru

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
LOL, Andy. I CHANGED mine (from round to rounddown)....why don't it work for ya?

#### XL Guru

>> why don't it work for ya?

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

Rgds/Happy Easter,
Andy

#### NSKL

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

Anne

#### XL Guru

>> =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

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. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On