0204 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 2

Advanced Excel Crash Course Section 2: Business Math Calculations
6 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.72
List Price:  €129.81
You save:  €37.09
£79.26
List Price:  £110.97
You save:  £31.71
CA$135.49
List Price:  CA$189.69
You save:  CA$54.20
A$153.49
List Price:  A$214.89
You save:  A$61.40
S$134.92
List Price:  S$188.90
You save:  S$53.97
HK$782.50
List Price:  HK$1,095.53
You save:  HK$313.03
CHF 90.16
List Price:  CHF 126.23
You save:  CHF 36.07
NOK kr1,085.06
List Price:  NOK kr1,519.13
You save:  NOK kr434.06
DKK kr691.90
List Price:  DKK kr968.69
You save:  DKK kr276.78
NZ$167.41
List Price:  NZ$234.39
You save:  NZ$66.97
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,980.73
List Price:  ৳15,373.46
You save:  ৳4,392.73
₹8,335.04
List Price:  ₹11,669.39
You save:  ₹3,334.35
RM472.40
List Price:  RM661.38
You save:  RM188.98
₦139,532.04
List Price:  ₦195,350.44
You save:  ₦55,818.40
₨27,813.53
List Price:  ₨38,940.06
You save:  ₨11,126.52
฿3,638.31
List Price:  ฿5,093.78
You save:  ฿1,455.47
₺3,237.16
List Price:  ₺4,532.16
You save:  ₺1,294.99
B$501.53
List Price:  B$702.17
You save:  B$200.63
R1,888.66
List Price:  R2,644.21
You save:  R755.54
Лв180.76
List Price:  Лв253.07
You save:  Лв72.31
₩134,741.70
List Price:  ₩188,643.77
You save:  ₩53,902.07
₪367.71
List Price:  ₪514.81
You save:  ₪147.10
₱5,617.33
List Price:  ₱7,864.49
You save:  ₱2,247.16
¥15,132.45
List Price:  ¥21,186.03
You save:  ¥6,053.58
MX$1,658.17
List Price:  MX$2,321.51
You save:  MX$663.33
QR364.88
List Price:  QR510.84
You save:  QR145.96
P1,378.06
List Price:  P1,929.34
You save:  P551.28
KSh13,256.67
List Price:  KSh18,559.87
You save:  KSh5,303.20
E£4,744.14
List Price:  E£6,641.99
You save:  E£1,897.84
ብር5,687.29
List Price:  ብር7,962.44
You save:  ብር2,275.14
Kz83,433.85
List Price:  Kz116,810.73
You save:  Kz33,376.88
CLP$98,290.17
List Price:  CLP$137,610.17
You save:  CLP$39,320
CN¥722.08
List Price:  CN¥1,010.95
You save:  CN¥288.86
RD$5,924.54
List Price:  RD$8,294.60
You save:  RD$2,370.05
DA13,479.97
List Price:  DA18,872.49
You save:  DA5,392.52
FJ$225.66
List Price:  FJ$315.94
You save:  FJ$90.27
Q780.13
List Price:  Q1,092.22
You save:  Q312.08
GY$20,949.30
List Price:  GY$29,329.86
You save:  GY$8,380.56
ISK kr13,941.60
List Price:  ISK kr19,518.80
You save:  ISK kr5,577.20
DH1,013.74
List Price:  DH1,419.27
You save:  DH405.53
L1,764.37
List Price:  L2,470.19
You save:  L705.82
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$806.29
List Price:  MOP$1,128.85
You save:  MOP$322.55
N$1,909.24
List Price:  N$2,673.01
You save:  N$763.77
C$3,682.20
List Price:  C$5,155.22
You save:  C$1,473.02
रु13,347.49
List Price:  रु18,687.02
You save:  रु5,339.53
S/372.08
List Price:  S/520.93
You save:  S/148.85
K382.87
List Price:  K536.04
You save:  K153.16
SAR375.15
List Price:  SAR525.22
You save:  SAR150.07
ZK2,481.24
List Price:  ZK3,473.83
You save:  ZK992.59
L460.94
List Price:  L645.33
You save:  L184.39
Kč2,346.52
List Price:  Kč3,285.22
You save:  Kč938.70
Ft36,532.34
List Price:  Ft51,146.74
You save:  Ft14,614.40
SEK kr1,069.05
List Price:  SEK kr1,496.71
You save:  SEK kr427.66
ARS$85,802.56
List Price:  ARS$120,127.02
You save:  ARS$34,324.45
Bs691.35
List Price:  Bs967.92
You save:  Bs276.56
COP$386,901.39
List Price:  COP$541,677.43
You save:  COP$154,776.03
₡50,320.66
List Price:  ₡70,450.94
You save:  ₡20,130.27
L2,469.73
List Price:  L3,457.73
You save:  L987.99
₲739,686.52
List Price:  ₲1,035,590.72
You save:  ₲295,904.20
$U3,756.16
List Price:  $U5,258.77
You save:  $U1,502.61
zł399
List Price:  zł558.62
You save:  zł159.61
Already have an account? Log In

Transcript

Hi there. This time, I'm going to talk about some of the advanced tricks related to round formula round up and round down. So I assumed that you would have either gone through our videos on round formula, or you may have already used round formula on one of the occasions in your work. Now, the question, let's assume that I'm giving you a number, which is in this case for triple 3.62. And I want an answer, which is rounded off to the nearest 50. Now, if you were to do this manually, you would have perhaps given this answer for 350.

Correct? Why? Because 4325 is the middle point between 4300 and 4350. And you've already crossed that hence 4350 is the answer I would have taken in case the number would have been 4313, then the nearest 50 would have been what four three double zero. So that's the intended objective. Now, if I write round formula, and I choose the number, and I put comma 50, this 50 doesn't mean rounded up.

2050 Means 50 number of decimal digits. So ideally, if you were a scientist, this would have been a very, very useful formula, which gives you 50 decimal accuracy for a certain number, but not this particular trick for us. What we want is nearest 50. And you might tell me Look, we already know about a formula, which is called m Brown. So m r, and then pressing the tab key, it asks me to choose a number which I do gladly, but then it also tells me please provide me the multiple to which you want the closest number to be. So if I give 15 those a bracket, enter, oh, I get the answer.

That's how easy it is. Wait. If I put a negative sign before the number and if I press enter, look, em round doesn't work. So if you're working with numbers, positive and negative, all mixed up, and you're anticipating an answer, which is has to be rounded off to me as 50 then do not use aim at all for this one simple reason. And there's one Reason, because m round doesn't have the branch of M, round up and M round down. So on occasions, you want to do a logic one, it should be rounded off to nearest 50, and that the lower part of the nearest 50 range.

So we will not take the help of M round doesn't work with positive and negative simultaneously, we will in fact, take the help of a technique very, very important trick because that important trick enables all the possible solution in one simple trick. So, equal to r o u, and being with the formula trick equal to r Are you pressing the tab key, choosing the number Now the trick begins here. I will divide with the number to which I want the closest number to be. Now you might ask me, why am I doing so let me show you why. I copy the selection. And I'm going to put a space before equal to so that the formula stays there.

And then I will show you this calculation separately that is be 18 divided by 50. Now currently, it's giving you 86 point six, seven. Okay. So inside the formula round, essentially, I have given 86.672 round formulas the raw material. Now when I put a comma and tell it zero, what does this mean? It means zero number of decimal digits, the answer is going to be rounded off to 87.

Now this is not the final answer. Final answer is when I multiply with five zeros, the same number with which I divided this is nearly 50. And the best part is if you have a negative number, it works with negative and positive numbers simultaneously with the same old formula unlike a round formula, which will get you trapped and you might also tell me Look, I am in the business of manufacturing cement, and I perhaps produced for triple three point 60 ton of semen. Now we are in the business where we are selling in lots of 25 units each. So I want to know How many complete units of lot can I sell us in wholesale market and the remaining amount that is not up to 25 lots I will be selling as loose quantities. So practically speaking, I wish to find out which is a nice 25, but at the lower end of nearest 25 or nearest 50.

In this case, let me apply around down. I choose the number divided by 25. As per our strategy I put a comma and zero and what should I do? Yes, correctly identified you should be multiplying with 25. So, it tells you that for three to five is the complete unit, which you can divide in lots of 2525 25 and can sell it off the remaining has to be sold as loose, and that quantity happens to be what for triple 3.6 to less for three to five. So that's where the advantage lies.

The round formula with our trick can also be combined with the round up and round down to give you desired results. In fact, if I show you more variation, for example, I want to round down to the nearest 20. Or maybe round up, you tell me and I have a solution for you. So in this case, I put an equal r o u n d, and then using round up pressing the tab key, choosing the base number divided by 20. Yes, comma zero is the standard format, and then multiplied with 20. So what you are now getting is rounded up to the nearest plenty.

And that's where the application lives round up, round down. And with our special trick, all combined together, you have all the possibilities where you might think about rounding to the nearest five round when is 50%, round to the nearest 50 cents, or round to the nearest 10 rupee or dollar. In case you wish to find out nearest 50 cents, I perhaps can use a normal round formula. Choose the number comma Wait, what am what am I missing? Yes, initially, you would be facing this particular dilemma because since you're not accustomed to this trick, you might miss this. So you put that comma for a little later.

First you divide by point five, comma zero, and then do what multiply with point five. So this has rounded off to the nearest 50 cents or 50%. Down, down, just add the word down, or maybe round up, depending on the logic. Hope you enjoyed this and see you in the next video with more subjects.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.