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 |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Neha Shah) are made up for this lesson on round family and rank.
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 |
About this data: Demo table only — not real customer or company data. Built to practise round family and rank.
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).
Loading practice tasks...
- Round the average (Easy): Find the average of the Amount column and round it to 0 decimals.
- Round up boxes (Easy): Order QKT-1002 has an Amount of ₹270. One bag holds ₹7 of items. How many bags are needed? Round up.
- Factory: defect % (Easy): On the Factory sheet, find the defect % of Line A: Defects ÷ Units made × 100. Round to 2 decimals.
- Factory: boxes needed (Easy): On the Factory sheet, one box holds 24 units. How many boxes does Line B need for its Units made? Round up.
- Round to nearest 10 (Medium): Round the Amount of order QKT-1007 (₹1,299) to the nearest 10.
- Nearest 50 (Medium): Round the Amount of order QKT-1004 (₹240) to the nearest 50.
- Factory: full boxes only (Medium): On the Factory sheet, how many FULL boxes of 24 can Line B fill from its Units made?
- Rank of an order (Hard): Find the rank of the Amount of order QKT-1002 among all Amount values (biggest = 1).
- Fastest delivery rank (Hard): Find the rank of the Mins of order QKT-1006 among all Mins values, where the fastest (smallest) is rank 1.
- Factory: rank of Line C (Hard): On the Factory sheet, find the rank of Line C by Units made (biggest = 1).
Worked example — round family and rank
Why
Sana Sheikh in Pune and Anna Kowalski abroad both need a clear, repeatable way to use round family and rank on a BrightLedger Finance sheet without breaking totals.
What
Round Family And Rank is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for round family and rank.
- Enter a small made-up value for Sana Sheikh using ₹ where money appears.
- Add a second value for Anna Kowalski using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once round family and rank is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
