MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Round Up to the Next 20

April 17, 2018 - by Bill Jelen

Round Up to the Next 20

Sandy works for a construction company. They sell flooring in packages of 20 Board Feet. When their estimators go to a job, they might say that they need 441 board feet of material. Sandy needs a way to always round up to the next 20.

As I was trying to solve this at one of my live Power Excel seminars, several functions flashed through my head:

The simplest rounding function is ROUND(Number to Round,Digits). To round something to dollars and cents, you would use =ROUND(A2,2). To round to whole dollars, you could use =ROUND(A2,0). What most people don't realize is that you can use ROUND to round to the nearest hundred or thousand. Instead of rounding to 2 digits, you could round to -2 digits and you would get the number in A2 rounded to the nearest 100. Or, =ROUND(A2,-3) would round to the nearest thousand.

continue reading »

More Reading

Summary of Summarize Data Week

Summary of Summarize Data Week »

April 14, 2018 - by Bill Jelen

For the last five days, I showed five different ways to create a customer summary from a detailed data set. Now it is time to vote for your favorite.

Summarize Data with Pivot Tables

Summarize Data with Pivot Tables »

April 13, 2018 - by Bill Jelen

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at using a pivot table to summarize the data.

read more news »

Featured Products

Learn to Write DAX

Learn to Write DAX »

Data analysis expressions (DAX) is the formula language of PowerPivot and Power BI. Simply reading a book is normally not enough to help Excel users learn DAX skills – you need to complete exercises and get plenty of practice to make the transition.

Excel 2016 in Depth

Excel 2016 in Depth »

Experienced with Excel? Don’t let Excel 2016 make you feel like a beginner again! This new full-color edition of the bestselling book has been completely overhauled. Gone is unnecessary and rarely used content; emphasis is on the most-used and new aspects of Excel 2016.

Power Pivot and Power BI

Power Pivot and Power BI »

Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data.

MrExcel LIVe

MrExcel LIVe »

A book for people who use Excel 40+ hours per week. Illustrated in full color.

M is for (DATA) MONKEY

M is for (DATA) MONKEY »

Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Pivot Table Data Crunching: Microsoft Excel 2016

Pivot Table Data Crunching: Microsoft Excel 2016 »

Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control! Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power.

see more products »