3.5 ROUND Family and RANK
| Function | Example | Result |
|---|---|---|
ROUND(number, digits) |
=ROUND(333.2857,2) |
333.29 |
ROUNDUP / ROUNDDOWN |
=ROUNDUP(12.1,0) / =ROUNDDOWN(12.9,0) |
13 / 12 |
ROUND to nearest 10 |
=ROUND(1299,-1) |
1300 |
MROUND(number, multiple) |
=MROUND(47,5) |
45 |
CEILING.MATH(number, significance) |
=CEILING.MATH(47,10) |
50 |
FLOOR.MATH(number, significance) |
=FLOOR.MATH(47,10) |
40 |
INT / TRUNC |
=INT(-2.5) / =TRUNC(-2.5) |
-3 / -2 |
RANK.EQ(number, ref, [order]) – 0 or omitted = largest gets rank 1; 1 = smallest gets rank 1. RANK.AVG gives average rank for ties. (Old RANK still works for compatibility.)
| Formula | Result |
|---|---|
=RANK.EQ(G3,$G$2:$G$11) (₹270) |
2 |
=RANK.EQ(H7,$H$2:$H$11,1) (8 mins, fastest) |
1 |
Ravindra Bagale's Tip
Many students "round" a number with the Decrease Decimal button – but that only changes the display; the value stays the same, and the totals end up a few paise off. If you really want to round, use the ROUND function. In RANK, don't forget to put $ on the range.
Ravindra Bagale's Tip – मराठी
बरेच students Decrease Decimal button ने number "round" करतात – पण त्याने फक्त display बदलतो, value तशीच राहते आणि totals मध्ये पैशांचा फरक येतो. खरंच round करायचं असेल तर ROUND function वापरा. RANK मध्ये range ला $ लावायला विसरू नका.
Ravindra Bagale's Tip – हिंदी
बहुत से students Decrease Decimal button से number "round" करते हैं – पर इससे सिर्फ़ display बदलता है, value वैसी ही रहती है और totals में पैसों का फ़र्क आ जाता है. सच में round करना हो तो ROUND function इस्तेमाल करो. RANK में range पर $ लगाना मत भूलना.
Practice task
Round every delivery fee to the nearest ₹5 with MROUND. Rank stores by sales (largest = 1) and by average delivery time (smallest = 1).