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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920