Rounding

As has been observed in many scientific and business circle, Precision would appear to always be preferable.  For example, .98977 is more precise that 1.0.  There are times, however, when precision is Not desirable, and may even not make much sense.

For instance, let’s say you are staffing for a project, and you have calculated that you need 11.92 people to complete the job.  Obviously, it would be difficult to find that .92 person (Please Note that we are not taking number of person-hours into consideration) so you Round up to 12.  The same may be said about currency, as fractions of a penny may not produce valid data.

 

You can, of course, use formatting in Excel that will make your numbers Appear to be rounded.  The trouble with relying on formatting, however, is that Excel retains the precision in the background, and any calculations will be based on that precision. If you are dealing with numbers representing currency that have four decimal places, you would obviously want to express the results in no more that 2 decimal places, so you can format for showing exactly that (the unwanted Precision will nonetheless be lurking in the invisible details)! 
 

So what is the solution? Enter the ROUND function!  The syntax for the ROUND function is:  ROUND( number, digits )

A Few Examples are Illustrated Below:

In the following worksheet, Column B contains the rounding functions:

1.      B1 has:  =ROUND(A1, 2) and therefore rounds to two decimal places.

2.      In B2, =ROUND(A1, 0) is being used to round to zero decimals.

3.      In B3, more extreme rounding is being achieved by using =ROUND(A3, -1)

4.      B4 uses the same function as B3 and applies it to B4:  =ROUND(A4, -1)

Note:  If you always want Excel to round up, you can use the ROUNDUP function.  In our illustration, this would result in B3 being 30 rather than 20.  The ROUNDDOWN function does (no surprise) just the opposite.

Sometimes Precision is simply not the best choice.  In such cases, use ROUND!

Pivot Tables – A Final Look (for now…)

As is apparent in our exploration of Pivot Tables in the last three weeks, they are powerful, versatile, and a quick way to do analysis on the fly. This week, we will wrap up this series with a quick look at some additional ways to garner more utility out of this vital tool.

Changing the Summary Function

Go to any cell within the Values section of your Pivot Table and Right-Click. Then select Summarize Values By and click on the calculation of your choice (Sum, Count, Average, etc).

Changing How Values are Shown

In a similar way as you change the Summary function, Right-Click any value cell. Then select Show Values As and choose the calculation you wish to use (you may be surprised at the extensive list…).

Give Your Pivot Table Some Color

Providing some Design and Color can go a long way to making your Pivot Table easier and more pleasant to use. It can also add a considerable touch of professionalism that is so often lacking. Simply click on any cell within your table and open the Design tab/ribbon. Note that you can choose from a variety of Styles, as well as Banded Rows, Banded Columns, and a surprising assortment of Layout options.

Pivot Tables. Powerful, Versatile, Quick. Give them a try…

Pivot Tables – A Third Look

As most seasoned Excel users know, there are multiple ways to Filter pivot tables. With the advent of Excel 2010, however, you have a new, powerful option to use to filter (or should we say, Slice) your data.

Slicers give you the choice of using labeled buttons you can click to filter your Pivot Table data on the fly. In addition to quick filtering, slicers also indicate the current filters being used, which, of course, makes it easier to understand what is being shown in a filtered Pivot Table report.

How-To Slice

1.   Select your table and then choose PivotTable Tools, Analyze, Insert Slicer

2.   Check the fields you want to filter, then click OK

3.   To Filter: Click items in any order

4.   To Clear a Filter, click the filter graphic

Multiple Pivot Tables

To filter multiple tables with a single slicer, select the slicer and then choose Slicer Tools, Options, Report Connections. To delete a slicer, simply select it and press Delete.

Slicers. Another great cooking tool for your Excel kitchen set!

Pivot Tables – A Second Look…

Last week we looked at a general overview of Pivot Tables. This week we will continue our examination of this powerful tool, by exploring a few rudimentary How-Tos:

Show or Hide the Pivot Table Fields Pane

To hide or show the Fields Pane, select any pivot table cell, and choose PivotTable Tools, Analyze, Show: Field List. Simple as that…

Refreshing a Pivot Table

Select any cell within the table and choose: PivotTable Tools, Analyze, Data: Refresh. Bamm, refreshed pivot table!

Add a Pivot Chart

Click any cell within an existing Pivot Table, and choose PivotTable Tools, Analyze, Tools: PivotChart. Then click OK after choosing the Chart Type and Subtype. Finally, it is a good practice to move the chart to another worksheet by clicking in the chart and choosing PivotTable Tools, Design, Move Chart: New Sheet [OK]. Piece of cake!

Knowing some simple techniques for manipulating Pivot Tables and Charts can take this vital tool from being a bit daunting to being a stress-free tool!