|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Round to Nearest 10 By Richard Rost 
6 years ago
Round a Number to the Nearest Multiple of 10 In this Microsoft Access tutorial, I'm going to show you how to round any number to the nearest 10, 100, etc. You'll also see how to round up, down, and avoid "banker's rounding." MembersThere is no Extended Cut for this video.
IntroIn this video, we'll talk about how to round numbers to the nearest 10 in Microsoft Access, and how to adjust the formulas to round to other values like 100, 1,000, or 5. I'll show you how to use the Round, Int, and supporting functions to round up, down, or to the nearest value, as well as how to work around Access's default bankers rounding if you want 0.5 to always round up. You'll see clear examples using a sample customer table and get practical formulas you can use in your own queries.TranscriptWelcome to another edition of TechHelp with Richard Rost. That's me, Richard Rost. Brought to you by AccessLearningZone.com.Today we're going to talk about rounding a number to the nearest 10. You can use this technique to round to the nearest 100, 1,000, 5, or whatever number you want. Jose asks, what function can I use to round 168 to 170? Basically, rounding to the nearest 10. You can use the round function. Unfortunately, round does not, by itself, give you the ability to round to a particular number, but with a little bit of math, you can do just that. The round function takes one, actually two parameters. One is the number you want to round. The second thing is how many decimal points. So with a little bit of math, we can round to zero decimal points and still accomplish what you want to accomplish. Here's some data. Here's a basic customer table. I added a field here called visits with some numbers in it - number of visits they've made to the office or whatever. 55, 143, 245. We want to round these to the nearest 10 for whatever reason. I've already made the query. Here we go. X is the number of visits. That's just to make my formula smaller. R is the number we're going to round to. I put that right in the query and I'll show you all these functions of the math in just a second. Here's rounding to the nearest 10. There is 60. 143 becomes 140. Two becomes zero, rounded down. 45 becomes 40. Access uses something called bankers rounding, which I have a whole other video on. If you're curious, basically what it means is if this number here, if the first digit is odd, it rounds up. If it's even, it rounds down. Bankers use that so that if you round everything that's 0.5 up, eventually that gets favored mathematically. But if you do the odd numbers one way and the even numbers the other way, then you get what's called bankers rounding. I'll show you how to avoid bankers rounding. If you want to make it so 0.5 always gets rounded up, I'll show you the function for that. There is round down, rounding the number down to the nearest 10. And here's always round up to the nearest 10. Let's take a look at the code. Here are the formulas right down here. X is my number of visits. R is the 10; that's whatever number you want to put in there. Round to the nearest, round down. Let me make it so you can see all these at once here. Round up. I'll copy and paste these in the description field below so you don't have to type them in off the video. There is the no bankers formula. That one's a little bit longer. Basically what we're doing here to round to the nearest is we're saying, take the number, whatever it is, divide it by R, and then round it, and then multiply it by R. Basically, you're going to take the number, divide it by 10, round it to zero decimal places, then multiply it by 10. So 55, for example, will become 5.5, round that, and then multiply it by 10 again. That's basically how you do it. Rounding down, instead of using the round function, we'll use the int function. That basically says, take the number, divide it by 10, and use the int function, which just chops off the fractional point. Everything after that point goes away. Then you multiply it by R again. Rounding up does the same thing, but we're using the negatives. No bankers basically adds a tiny little fractional component, .00001, to the number itself. That way, .5 is always just slightly higher than .5. It's not exactly .5, so it avoids the bankers rounding. Then when you put it all in there, you get the right values. That's it. Thanks for watching. Hope you learned something. If you like getting tips like this, make sure you subscribe to my channel and click the little bell icon. Make sure you get notifications whenever I release a new video. If you have a question you want to see answered right here, visit my TechHelp page and post it there. You can also email me, but the TechHelp page is better. Make sure you find my Access Learning Zone discussion group on Facebook. There's my email address. Here are all my other links: Facebook, Twitter, YouTube, my blog. I'll put all this stuff in the description below. You can watch my free Access Level 1 course right here on YouTube. It's three hours long, absolutely free. If you like it, Level 2 is just one dollar. Thanks a lot. We'll see you next time. QuizQ1. What is the main topic discussed in this video?A. Rounding a number to the nearest 10 (or any chosen value) B. Sorting numbers in ascending order C. Generating random numbers D. Calculating averages Q2. What function is used in Access to round numbers? A. AVG B. SUM C. ROUND D. INT Q3. Why does the standard ROUND function in Access not allow rounding directly to the nearest 10, 100, or other base? A. It only rounds to negative numbers B. It lacks an option for rounding to a specific base value C. It cannot handle decimal numbers D. It only works with text fields Q4. What is the formula to round numbers to the nearest specified value (like 10) in Access? A. Divide by R, round to zero decimals, then multiply by R B. Multiply by R, then round C. Add R, then divide by 2 D. Subtract R from the number, then round Q5. What is R in the formula used to round numbers? A. The decimal place B. The original number C. The number to round to (e.g., 10, 100) D. The number of records Q6. What does the INT function do in Access? A. Converts text to uppercase B. Multiplies numbers C. Rounds down to the nearest integer D. Returns the remainder after division Q7. What is "bankers rounding"? A. Rounding with always rounding up B. Rounding where 0.5 is always rounded down C. Rounding where .5 values are rounded up or down based on the digit's parity D. A method of rounding only used in banks Q8. How do you avoid banker's rounding if you want .5 to always round up? A. Use the SUM function B. Add a small fractional amount (like .00001) to the number before rounding C. Multiply the number by 10 D. Subtract 1 before rounding Q9. What is the benefit of dividing the number by the rounding base (such as 10), rounding, and then multiplying back? A. It filters the highest numbers first B. It allows you to round to any base you choose (e.g., 10, 100) C. It sorts records D. It calculates averages Q10. In Access, to always round a number down to the nearest 10, which function is used? A. ROUND B. INT C. MOD D. SUM Answers: 1-A; 2-C; 3-B; 4-A; 5-C; 6-C; 7-C; 8-B; 9-B; 10-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to round numbers to the nearest 10 in Microsoft Access. This technique is helpful if you need to round to any value, such as the nearest 100, 1000, or even numbers like 5, depending on your needs.The question came up of how to round a number like 168 up to 170, essentially rounding to the nearest 10. The built-in Round function in Access does not directly provide a way to round to a specific value like 10, but you can achieve this with a bit of math. The Round function takes two parameters. The first is the number you want to round, and the second is the number of decimal places. With some basic calculations, you can use the Round function to round numbers to the right place even if you're not just dealing with decimal values. For example, imagine a customer table that includes a field for the number of visits each customer has made. Let's say the visits are 55, 143, 245, and so on. If you want to round each of these values to the nearest 10, you can set up a query to do so. In the query, you can set up variables for easier calculations. Let X represent the number of visits, and let R represent the value you're rounding to (like 10). With these definitions, you can write formulas to round to the nearest value, always round down, or always round up. Rounding to the nearest 10 works as expected. For example, 55 becomes 60, 143 becomes 140, 2 rounds down to 0, and 45 becomes 40. One important thing to note is that Access uses what is called bankers rounding. In this method, if you have a value ending in .5, it is rounded up if the previous digit is odd and rounded down if it is even. This approach is used in banking and prevents statistical bias over many calculations. If you want to avoid bankers rounding and always round .5 up, you can adjust your formula accordingly. To always round down to the nearest 10, use a function that simply removes the fractional part, which leaves you with the lower value. To round up, you adjust your formula to ensure the value always goes to the next higher multiple. The formulas themselves use a combination of dividing by the desired value (such as 10), then rounding, and then multiplying back. For instance, to round 55 to the nearest 10, divide by 10 to get 5.5, round that to 6, and then multiply back by 10 to get 60. If you want to avoid bankers rounding, you can tweak your formula by adding a very small number, like .00001, so values at .5 are always treated as slightly higher than .5. This ensures your rounding method always goes the way you want. If you are interested in the specific expressions, I include details on how all of these calculations work in my full tutorial. You will also learn about using the Int function for rounding down and handling negative numbers when always rounding up. If you found this tip useful, be sure to check out more resources on my site. If you have a specific question you'd like answered, please visit my TechHelp page to submit it. You can also join the Access Learning Zone discussion group on Facebook and connect with me on social media. There is a free three-hour Access Level 1 course available on YouTube if you are looking to get started, and the second level is just one dollar if you want to continue. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListRounding numbers to the nearest 10 in AccessUsing the Round function to round to a specific number Understanding how the Round function parameters work Creating a query to round numbers in a table Explanation of bankers rounding in Access Using a formula to always round .5 up (avoiding bankers rounding) Rounding numbers down to the nearest 10 using the Int function Rounding numbers up to the nearest 10 Customizing the rounding to any desired increment (e.g., 100, 5) Step-by-step walkthrough of formulas for rounding rounding down and rounding up |
||||
|
| |||
| Keywords: TechHelp Access bankers rounding round rounding access tips techhelp round to PermaLink Round to Nearest 10 in Microsoft Access |
