Tag Archives: Formulas

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

Generating Random Numbers and Letters

Need to generate a random number? Excel has two formula variations that can do that for you.

The first is RAND, it generates a random value between 0 and 1. But if you need larger values, try RANDBETWEEN.

RANDBETWEEN generates random numbers between a bottom and top value that you specify.

Lastly, if you need to generate a random letter value – try this formula: =CHAR(RANDBETWEEN(65,90))

In this formula the CHAR function returns the character symbol specified by a number. Letters A-Z are between 65 and 90.

 

 

Highlighting the Current Year

Here is a version of a spreadsheet that I’ve been using for a couple of years to track and plan capital purchases. A number of people review this spreadsheet and I want to make it as easy as possible for them to read the spreadsheet. You’ll notice that the Budget year is highlighted in green and items being purchased in that year are highlighted as well. This is accomplished with our friend conditional formatting and the following spreadsheet functions:

  • ADDRESS
  • ROW
  • COLUMN
  • MATCH
  • INDIRECT
  • ISBLANK
Spreadsheet with automatic highlighting
Capital Budget Spreadsheet – note the row and column (year) highlights

This spreadsheet makes use of a helper column of formulas. Rows where the value equals TRUE are highlighted.

The Helper column holds formulas
The Helper column holds formulas

Cells give a value of TRUE when there is a value for that row in the Budget Year selected in cell A1. You can see that 2017 has been selected as the Budget Year and that rows 9 and 20 have a value for that year and are highlighted as a result.

This is the formula that returns the value

=ISBLANK(INDIRECT(ADDRESS(ROW(),MATCH($A$1,$F$2:$AU$2,0)+5,3,TRUE)))=FALSE

Working from the interior of the formula outward.

MATCH($A$1,$F$2:$AU$2,0)

This looks for a match between the value in A1 and the Budget year headings which start in cell F2 and go to AU2 (the year 2033, which is incredibly optimistic – but that is another story). MATCH returns the number of the first item in that array of cells that matches the value in A1. This is why even though there are two columns for every year (a Budget column and an Actual column) MATCH will only return the Budget column, as it is the first value to match.

So the result of MATCH($A$1,$F$2:$AU$2,0) is 9

However, if I actually want to capture the column I need to to add 5 to compensate for the fact I have 5 columns (A-E) before column F and the year headings begin.  This is why I’m adding 5 in the formula.

MATCH($A$1,$F$2:$AU$2,0)+5 =14

In the next step I use ADDRESS and ROW to capture the address of the cell I’m testing.

ADDRESS(ROW(),14,3,TRUE))

ROW() captures the value of the row of the cell where the formula is written. If the formula is in A3, then row() returns 3.

ADDRESS turns the cell address of the referenced cell (not its’ contents). In our example; ADDRESS(3,14,3,TRUE)=”$N3″

The ISBLANK function in the next step has a bit of a hiccup with that “$N3” string, so we use INDIRECT to convert that string to something ISBLANK can understand.

Finally, ISBLANK is used to test if there is a value in the referenced cell or not. If there is nothing in the cell ISBLANK = TRUE.

If ISBLANK = TRUE, then the last portion of the formula looks like this: TRUE does not equal FALSE, so the result of the formula in cell A3 is FALSE.

I could have put that formula into the conditional formatting dialogue – but for clarity and ease of working I choose to make the helper column instead.

In the conditional formatting dialogue I’ve used the following formula =$A3=TRUE

I’m using a simpler version of the formula in the conditional formatting dialogue to highlight the year.

=MATCH($A1,$F$2:$AU$2,0)+5=COLUMN()

In this case I find the column number of the year and test to see if it matches the column number of the current cell. If it does then the cell receives a green highlight fill.

Cell A1 uses Data Validation to offer the user a nice drop-down list of years.

Excel: Concatenation for the Social Nation

Concatenation results
Concatenation results

As I’ve worked more with scheduling posts, tweets and pins, I’m trying to make the most of the Subject line used by Google Calendar.

Google Calendar Subject line
Google Calendar Subject line

I’ve found that if I combine a meaningful keyword describing the post(or tweet, or pin) plus the phrase that triggers the IFTTT action, then managing the scheduled posts once they are uploaded into Google Calendar is a bit easier. It also makes it easier when I’m filtering and managing the spreadsheet too.

In my spreadsheet I use a separate column each for subject keyword and for subject trigger phrases (actually I’m paring those down to keywords too). But I want them joined together to create the actual subjects. To do this, I use the Excel CONCATENATION function. Which is most simply represented by the & symbol. In the example at the beginning of the post you can see the formula:

=B103& ” ” &C103

In this case I’m using the & symbol to join the values of cells B103 and C103 together with the string ” ” in the middle to create a nice space between words. This allows the subject phrase to be created automatically once I’ve selected the subject and trigger keywords.