Category Archives: Formula Example

Nail Down That Date!

Passing a spreadsheet around between organizations has a hidden problem; one that can easily make trouble. And the trouble comes, not from the spreadsheet, but from the default date setting on the computer.

Excel uses the default date setting to interpret the order of date information. Whether its’ Month-Day-Year or Day-Month-Year, or even Year-Month-Day, that information comes from the OS date settings. These are settings that we don’t often think about once we’ve set them. And typically, they are the same throughout an organization.

But take the spreadsheet you’ve designed, that uses Month-Day-Year into a Day-Month-Year organization, and all sorts of problems crop up.

The first problem is that you might not notice immediately; if July 6, turns into June 7 – that might not jump out at you as a problem. If you are lucky, you’ll spot something weird about the 12th of Month 21 …

So how do you nail down those dates so they can’t shift? One strategy is to break up your date entry into your preferred format, and then rebuild the date using the DATE function.

The syntax for the DATE function is =DATE(year, month, day)

The Date Function
Using the Date function to reassemble a date from separate cells

Here you can see the DATE function is building a date from the values in three separate cells: A3, B3 and C3 and the formula looks like this =DATE($C3,$B3,$A3)

Another advantage of this strategy is that Data Validation can be applied to these cells; ie the day column can be restricted to whole numbers between 1 and 31, the month column to whole numbers between 1 and 12 and the year column as well. In the sample file I’m using, the column holding the complete date (D) is hidden from the user. They will only see columns A thru C. The complete (and correct) date is referenced in formulas.

An alternate strategy would be to use the DATE function to extract the correct order from a whole date typed into a cell. In this case you would need to rely on the users to enter the date consistently regardless of their date system. I would recommend a custom date format be applied and a comment to tell the user what the required date format is. Breaking the date up avoids this reliance on the user’s compliance.

Eat Your Waffles

Ok, don’t eat the waffle chart

In a previous post, I discussed making a Button Bar Chart. That whole process really inspired me to think about simplified charts for presentations.

Which got me thinking about Waffle Charts.

4 category waffle chart
Note how the smallest group stands out

Waffle charts are excellent for looking at data sets where the smallest numbers are the important ones. You can use colour (as I have above) to make those numbers stand out.

But oddly, I don’t see people using a lot of waffle charts in their presentations. And there is no template for a waffle chart in Excel.

You can find some interesting ideas about building Excel waffle charts for dashboard purposes and I recommend this article to you: Interactive Waffle Charts in Excel

However, I was looking for something different. Something that wouldn’t have me counting and colouring cells manually (shudder).

Building the Waffle

I chose to build the waffle chart using a series of conditional formatting rules. The first step was creating the formula to count the cells of the waffle.

Waffle chart base formula
Counting the cells in a 100 grid waffle

In case the picture is a bit small, the formula used here is:
=(MOD(ROW()+8,10)*10)+(COLUMN()-2)+1

This uses the row and column position of the cell to count from 1 to 100 in a 10 by 10 grid.

I then built on that base formula with this monster formula:

=IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=’5 Category Waffle’!$A$2,’5 Category Waffle’!$A$2, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=(‘5 Category Waffle’!$A$2+’5 Category Waffle’!$A$3),’5 Category Waffle’!$A$3, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$4),’5 Category Waffle’!$A$4, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$5),’5 Category Waffle’!$A$5, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$6),’5 Category Waffle’!$A$6,0)))))

The sheet BTW is called 5 Category Waffle.

5 category formula
The 5 category waffle formula result

The formula checks the position number of the cell generated by the base formula and sees if it is less than or equal to the number of values in each category in column A. It then returns the value of the category in each cell.

Because I wanted to put symbols in the cell like these examples.

talking head icon used in waffle chart
Talking Heads waffle chart
bomb icon waffle chart
Bombs waffle chart

I took that monster formula and made it into a named formula.

 

 

This made building the conditional formatting rules much easier to do(simply because the conditional formatting dialog is so cramped).

Lastly, I built a series of conditional formatting rules to change the background colour of the cell based on the value returned by the formula. For the waffles using symbols, the rule formats the colour of the font, instead of the background.

A couple of additional pointers

  • To create a perfect grid, switch the view in Excel to Page Layout View. Page Layout View uses the same measurement scale for both row height and column width.  Set your measurements here.
  • For the symbol waffles, use the File> Options>Advanced> Display Options for this worksheet and turn off the display of gridlines. That way when you copy the waffle, the gridlines will be invisible.

 

Excel – Total at the Top

It’s that time of year again. Tax time. Now that I’ve made you sad, lets check out a formula which is very useful when adding up long columns of numbers (deductions anyone?)

Column of numbers, with accompanying total
Put the sum of the column at the TOP of the page, making it easy to find

When you have long columns of numbers of irregular length, it is often easier to have the totals display at the TOP of the page. That way, you don’t have to scroll all over the place to find them. The other advantage of this formula is that you can use it to keep an eye on your total as it accumulates, since you don’t have to know how many rows long the column will be.

The formula here is =SUM(OFFSET(A1,1,0,COUNT($A:$A),1))

Where the OFFSET & COUNT functions are used to create the range that will be summed.

The syntax for OFFSET
OFFSET(reference, rows, cols, [height], [width])
OFFSET(A1,1,0,COUNT($A:$A),1)

  • A1 is the reference cell,
  • Rows – the range begins 1 row down from A1,
  • Cols – the range begins in the same column 0,
  • Height – the number of rows to be included are counted using the COUNT function, COUNT($A:$A) counts the cells in Column A with numbers in them
  • Width – is set to 1 column (column A) wide

I have to say there is something very satisfying about seeing that total increase as each number is entered in the column.

featured image by
Antoine Dautry