Excel - Conditionally Round Based on Adjacent Category - Excel - Episode 1359

816 views · Published 11 March 2011 · 4:32 · Indexed 31 October 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel Rounding to Different Decimals Based on Category.

Welcome back to another exciting episode of Dueling Excel, where Mike "Gel" Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head-to-head to find the best solution to your Excel problems. In this episode, we are live at Highline Community College, and I have a killer solution for a rounding problem that I can't wait to share with Mike.

The problem at hand is that we need to round numbers, but with a twist - any number in the "Dollar" category should be rounded to 2 decimal places, while any number in the "Crude" category should be rounded to 4 decimal places. My solution involves using the IF function within the ROUND function, where we check if the category is equal to "Dollar" and then round to 2 decimal places, otherwise round to 4 decimal places. However, this solution assumes that there are only two categories - "Dollar" and "Crude" - in the column.

But what if there are more categories that require different rounding? That's where my second solution comes in. We can create a table with the categories and their corresponding rounding values, and then use the VLOOKUP function within the ROUND function to dynamically round the numbers based on their category. This solution is more versatile and can accommodate any number of categories.

However, upon further inspection, I realized that the person who sent in this question may not actually need the numbers rounded, but rather just displayed as rounded. In that case, we can use Conditional Formatting to achieve the desired result. By using a formula that checks the category and formatting the cells accordingly, we can display the numbers with the correct number of decimal places without actually rounding them.

And the best part? This trick is only possible in Excel 2007 and later versions, making it a valuable tool to have in your Excel arsenal. So join us as we dive into the world of Conditional Formatting and discover new and amazing ways to use this powerful feature. Don't forget to subscribe to our channels for more exciting episodes of Dueling Excel from MrExcel and Excel Is Fun. See you next time!

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/


Table of Contents:
(00:00) Conditionally Round in Excel
(00:21) Solution using IF function
(01:15) Alternative solution using VLOOKUP
(02:22) Displaying rounded numbers using Conditional Formatting
(03:34) Explanation of the trick used in Conditional Formatting
(04:11) 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:
Conditional formatting with formulas
Displaying decimals in Excel
Excel 2007 features
Excel conditional formatting
Excel number formatting
Excel rounding formulas
Excel tips and tricks
IF function in Excel
MrExcel and Excel Is Fun dueling excel podcast
Using formulas in conditional formatting
VLOOKUP function in Excel


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

Bill thinks he has a 'killer solution' for this Dueling Podcast - lets find out! Today, in Episode #1359, Mike 'ExcelIsFun' Girvin and Bill 'MrExcel' Jelen find solutions that will Round Dollars to (2) decimals, but Round Crude to (4) Decimals.

More from this channel