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.
How to round up in excel and/or access
- Status
1 - 10 of 10 Posts
Joined
·
11,836 Posts
First Name -
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.
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
·
2,708 Posts
For Access, try this link -
http://www.google.co.uk/groups?hl=e...e=UTF-8&q=round+nearest+nine+group%3A*access*
For Excel, try
=IF(OR(MOD(A1,0.1)=0,MOD(A1,0.5)=0),ROUNDUP(A1/0.1,0)*0.1+0.09,ROUNDUP(A1/0.1,0)*0.1-0.01)
Rgds/Happy Easter,
Andy
http://www.google.co.uk/groups?hl=e...e=UTF-8&q=round+nearest+nine+group%3A*access*
For Excel, try
=IF(OR(MOD(A1,0.1)=0,MOD(A1,0.5)=0),ROUNDUP(A1/0.1,0)*0.1+0.09,ROUNDUP(A1/0.1,0)*0.1-0.01)
Rgds/Happy Easter,
Andy
Joined
·
2,708 Posts
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
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
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
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
·
2,708 Posts
>> =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
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
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.
1 - 10 of 10 Posts
- Status
Join the discussion
