More Rounding…

Last week we discussed the use of the CEILING and FLOOR rounding functions. Those are, of course, Not All that is available when it comes to controlling your Precision in Excel. We will look at the INT, EVEN, and ODD functions.

=INT(number)

This function rounds the number down to the nearest integer. Many possible applications, including staffing of projects.

=Even(number)

Using this function, your number rounds away from zero to the nearest even integer. If your staffing requires pairs of individuals working together, this may be just your ticket.

=ODD(number)

As you might expect, this function always rounds away from zero to the nearest odd integer. Undoubtedly useful in certain Odd situations (sorry, I couldn’t resist…).

INT, EVEN, and ODD. More ways to round your data your way in Excel.

Ceiling & Floor

When Excel is displaying slightly different values than what is actually stored in the cells that you are working on, it can be beneficial to use one of the several Rounding functions. Two of these rounding functions are CEILING and FLOOR.

The use of a rounding function is often recommended, especially if the numbers you are investigating are the result of multiple mathematical calculations. Multiple calculations may, of course, result in the introduction of rounding errors. These behind-the-scenes errors can cause small inaccuracies which may be very significant when precision is necessary.

The syntax for CEILING is CEILING(Number, Significance) where

·         Number is the number being rounded, and

·         Significance is the number of additional integers to which the number is being rounded.

The following Examples make this a bit clearer:

·         = CEILING(20.3, 1) = 21

·         = CEILING(20.3, 5) = 25

·         = CEILING(-19.9, 2) = -18

The FLOOR function does the very same thing as the CEILING function; however it always rounds down.

CEILING and FLOOR. Two more very useful functions for your Excel toolbelt.

Time Management with Excel

I admit that I have been a bit overwhelmed with work lately, and as an instructor and avid proponent of Time Management, I find this slightly upsetting. If, in fact, you employ good time management techniques, you can very directly determine what you should be working on and when. By the way, Ready for Anything by David Allen is an excellent book on this subject.

But how can Excel help in this regard? Well, let’s say that you have your Calendar and To-Do Lists in PDF form. You can import data in this formant directly into an Excel spreadsheet using a PDF to Excel Converter. The result is that you to easily keep track of key dates or to-do lists. You can eliminate a plethora of reminder notes and other calendars. In the process, you’ll also save paper and time, (Time Management, get it?...)

A large project with several professionals collaborating can use this Excel spreadsheet as a database for Project Management. Share the spreadsheet, and everyone can add their own information. You can include data such as availability, contact info, element ideas, resource tools, and so forth. For starters, check out the many If you are organized, you won’t be sitting at your desk wondering what to do next and, that my friends, is a very good thing.

Sparklines Series # 5

This week we are concluding our examination of Sparklines with this fifth installment of our series on this remarkably versatile Excel tool.

In this series we have explored the basics of creating Sparklines, changing their type, highlighting values, adding text, as well as changing the color and weight. In this final week will take a look at how to handle Hidden and Empty Cells in Sparklines.

Let’s examine the procedure of managing these potentially troublesome hidden and empty cells:

1.   Select the Sparkline group

2.   From the left side of the Sparkline Tools/Design ribbon, click on Edit Data

3.   Select Hidden & Empty Cells

4.   You will be presented with Hidden and Empty Cell Settings

5.   Choose Show Empty Cells:

·       Gaps - Show a space between points

·       Zero: Show as a zero (0) value

·       Connect data points with line: Connects the points before and after the empty cell

Try giving Sparklines a try sometime. You may be surprised at how efficient they are to create, and how well they will be received by your users.

Sparklines Series #4

This week we are continuing with the fourth installment of our series on the surprisingly versatile and useful Excel tool called Sparklines.

As we have noted, these Miniature Charts fit into a single cell or range in your worksheet and, as with any chart, they provide you with a visual representation of your data. The beauty of Sparklines, of course is that they provide this visual representation without having to resort to a full-blown chart.

Let’s see what else we can do with these Miniature Marvels. For instance, let’s say that you want to add a little bit more Visual Pizzazz to your Sparkline group. Here is what you do:

Change the Color and Weight of Your Sparklines

1.   First select a Sparkline or Sparkline group, and then choose Sparkline Tools/Design

2.   Then select a style of your choosing to change both the Sparkline and the Marker color

3.   Alternatively, you can change just the Sparkline color and weight

Customize the Axis Settings

1.   Select the Sparkline or Sparkline group

2.   Choose Sparkline Tools/Design

3.   Choose the Axis and options of your choice

Sparklines. Who knew all the wonders of the small but powerful tools?