Rounding Formulas in Excel
Before learning the tips, first lets understand various rounding formulas & what they do. Look at this:| Formula | What it does? |
| ROUND | Rounds a number to specified decimal points (or multiples of 10) |
| ROUNDUP | Rounds up a number |
| ROUNDDOWN | Rounds down a number |
| MROUND | Rounds to nearest multiple of specified number |
| INT | Rounds down to nearest integer |
| TRUNC | Gives you only integer portion |
| CEILING | Rounds up a number to nearest multiple of 1,10,100… |
| FLOOR | Rounds down a number to nearest multiple of 1,10,100… |
| EVEN | Gives next even number |
| ODD | Gives next odd number |
| FIXED | Rounds and converts to text format (with commas if you want) |
18 Rounding Formula Tips
1. Round to 2 decimal points
Example: 1.2649 to 1.26=ROUND(A1,2) Rounds value in A1 by 2 decimal points
2. Round up to 2 decimal points
Example: 1.2649 to 1.27=ROUNDUP(A1,2) Roundsup value in A1 by 2 decimal points (ie away from zero)
3. Round to nearest integer
Example: 1.2649 to 1=ROUND(A1,0) By using 0, we can round the value to nearest integer
4. Round to nearest multiple of 10
Example: 544.234 to 540=ROUND(A1,-1) By using negative numbers, we can round the value to nearest multiple of 10, 100…
5. Round up to nearest multiple of 10
Example: 544.234 to 550=ROUNDUP(A1,-1)
6. Round to nearest thousand
Example: 312789123 to 312789000=ROUND(A1,-3)
7. Round to nearest million with one decimal point
Example: 312789123 to 312.8=ROUND(A1/1000000,1) First we divide the number by million (1,000,000) and then round this to 1 decimal point.
8. Round to nearest multiple of 2
Example: 43 to 44=MROUND(A1,2) Just like round formula, but for any multiple. So MROUND(A1,2) takes value in A1 and rounds it to nearest multiple of 2
9. Round to nearest multiple of 5
Example: 93 to 95=MROUND(A1,5)
10. Round down to hundred
Example: 301 to 300=FLOOR(A1,100) To round down, we can use FLOOR formula.
11. Get only the integer portion of a number
Example: -23.34 to -23=TRUNC(A1,0) To extract only the integer portion of number, use TRUNC formula. Note: INT formula gives same result for positive numbers.
12. Round a number to 2 decimals and convert to text
Example: 312789.26921 to 312,789.27=FIXED(A1,2,FALSE) In one shot, round and convert the number to text. Useful when you want text output.
13. Get next even number
Example: 42.1 to 44=EVEN(A1) Gets you next EVEN number (away from zero)
14. Get next odd number
Example: 44.93 to 45=ODD(A1)
15. Round to nearest quarter ($0.25)
Example: 19.14 to 19.25=MROUND(A1,0.25) MROUND can be used with fractions too.
16. Round to next 9 (ie 19,29,39 etc.)
Example: 23 to 29=ROUNDUP(A1,-1)-1 To do this, we just roundup the number to next 10 and then subtract 1 from it.
17. Round up to next 1000
Example: 124567 to 125000=CEILING(A1,1000) Just like FLOOR, but takes you to next value.
18. Get only decimal portion of a number
Example: 23.345 to 0.345=A1-TRUNC(A1) To get only decimal portion, subtract TRUNC value from original
Download Rounding formula example workbook
Click here to download example workbook & understand these formulas better.What about .2 tips?
Well, those are for you to fill down. Go ahead and write formulas for both these situations & you have the .2 tips!18.1 Waiter friendly pricing
Lets say you run a hotel where customers usually tip 15% of bill amount. Now, to make it easy, you want to price your items such that when 15% is added, the total amount becomes a round number like $1.00, $2.00 etc.For example: If a dish’s current price is $2.50, then 15% tip on it would be $0.37. This makes the total $2.87.
If you modify the price to $2.60, with tip the total would be $3.00.
Assuming current price of a dish is in A1, what formula will give you new price?
18.2 Rounding to nearest Monday
Lets say you have some dates in a list and you want them to round to nearest Monday. Assuming you have a date in A1, what formula gives nearest Monday?Go ahead and figure them out. Post your answers using comments.
Click here to comment.
0 comments:
Post a Comment