Rounding down to nearest 100

SantasLittleHelper

Board Regular
Joined
Nov 25, 2016
Messages
77
I have a calculated field with the following Expression:
Code:
Round([RecNo],-2)

The RecNo field is a regular autonumber field. I want the new calculated field to round the autonumber down to the nearest 100.

E.g.

AutoNumberCalculated Field
780
150100
399300
15421500

<tbody>
</tbody>


With the current expression, I get #func ! error in each record
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Looks like MSAccess doesn't accept negative inputs for the second parameter. I don't think round would work anyway (since you would be rounding up or down based on the value - you really want a floor function here).

You can try:
(Int([RecNo]/100))*100

Not tested for negative RecNo (which I assume don't exist).
---------------
|    1 |    0 |
---------------
|  401 |  400 |
|   78 |    0 |
|  150 |  100 |
|  399 |  300 |
| 1542 | 1500 |
|  400 |  400 |
|    0 |    0 |
---------------


Further rounding info:
http://allenbrowne.com/round.html
 
Upvote 0
If this is related to your other question, regarding trying to break up your records in groups of 100, note the dangers of using an Autonumber field to do that (as I mentioned in your other thread).
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top