Look Who it Was Random video

Excel - Mastering Indian Pay Rounding in Excel: Step-by-Step Tutorial - Episode 1042

651 views · Published 23 June 2009 · 2:50 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Indian Pay Rounding in Excel: Step-by-Step Tutorial.

Welcome back to the MrExcel netcast where we dive into all things Excel. In this episode, we're tackling a common issue faced by many Excel users - rounding. Specifically, we'll be looking at the Indian Pay Rounding system and how to handle it in Excel. 

This question was sent in by Gopal from India, who is dealing with a new ruling by the Sixth Indian Pay Commission. The ruling states that any amount in excess of ten dollars, up to and including the decimal 0.99, should be rounded down to the last ten dollars. However, once the amount goes above that, for example, $21, it should be rounded up to the next ten. This can be a tricky situation to handle in Excel, as none of the built-in rounding functions are designed to handle this specific scenario. 

But fear not, because in this video, I'll be showing you a simple solution using the MOD function. This function takes a number and divides it by a divisor, then reports the remainder. So, for example, 24 MOD 20 will give us a remainder of 4. What's interesting is that this function also works with decimals, which is exactly what we need for the Indian Pay Rounding system. 

To solve this problem, we'll be using an IF statement with the MOD function. If the remainder is less than or equal to 0.99, we want to round down to the last ten dollars. To do this, we'll use the INT or TRUNC function to remove the decimal portion. If the remainder is greater than 0.99, we want to round up to the next ten dollars. To do this, we'll use the ROUND function with a negative number of digits, which will round to the nearest ten. And just like that, we have a solution to the Indian Pay Rounding system in Excel. 

I hope you found this tutorial helpful and that it saves you time and frustration in your Excel work. Don't forget to subscribe to the MrExcel netcast for more Excel tips and tricks. Thanks for watching and we'll see you in the next video!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Another question from Gopal. New India Pay Commission guidelines suggests that pay should be rounded to increments of 10. If the amount over the last 10 is less than or equal to 0.99, then round down. Otherwise round up. While Excel’s built-in functions don’t handle this, Episode 1042 shows you how to handle this using three functions in Excel.

This is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new video every workday to learn one of 377 tips from the book!

Table of Contents:
(00:00) Introduction
(00:10) Rounding Question from Gopal
(00:21) Using the MOD Function
(01:23) Creating the Rounding Formula
(02:18) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Excel rounding functions
MrExcel netcast on rounding in Excel
Pivot table analysis in Excel
Rounding down to the last ten in Excel
Rounding in Excel with MOD function
Rounding to the nearest dollar in Excel
Rounding to the nearest penny in Excel
Rounding to the nearest ten cents in Excel
Rounding up to the next ten in Excel
Using INT function for rounding in Excel
Using TRUNC function for rounding in Excel
YouTube video on rounding in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152479/

More from this channel