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?)
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.
Here is a common scenario for me. I like to apply alternate line formatting on spreadsheets, especially wide spreadsheets. It makes it easier to follow the correct row across the spreadsheet. Here a blue fill is applied to alternate lines.
Alternate line formatting
But in this spreadsheet, I wanted to do something a little different. I wanted the alternate line formatting only be applied once the date is entered.
The Alternate Line formatting is not applied until a date is entered.
This means I need to find the last row in the date column. Now previously I’ve used a COUNTIF formula, to count the number of rows with data. Then subtract any blank rows at the top of the spreadsheet. The drawback of this formula, is that editing the spreadsheet later often means the COUNTIF is no longer correct. Gaps in the data also cause problems.
Enter the AGGREGATE Function
The AGGREGATE function was introduced in Excel 2010. Bill Jelen called it “SUBTOTAL on steroids”. It offers 8 more functions than SUBTOTAL and the ability to ignore hidden rows and error values. You can use it either in a Reference or Array form. Here is a link to the MS Support article. One of the new functions offered is LARGE.
So this formula uses =AGGREGATE(14,6,ROW(A:A)/(NOT(ISBLANK(A:A))),1)
14 – refers to the LARGE function
6 – tells it to ignore error values
ROW(A:A)/(NOT(ISBLANK(A:A))) creates an array of the results of each row number in column A divided by whether the NOT(ISBLANK) portion of the formula returns a TRUE or FALSE answer. TRUE is evaluated as 1 and FALSE as 0. As division by zero creates an error, any blank cells are ignored.
1 – indicates that the LARGE function will return the largest number from the array. Hence, the last row.
The Aggregate function finds the last row The formula correctly finds the last row, in spite of gaps in the data.
And things came to a grinding halt…
At this point, I did what I normally do and made my AGGREGATE formula into a Named Formula. I typically find this is to be a tidier way of working. When I did this; Excel froze. Repeatedly. Whenever, I did anything that caused a recalculation. Like clicking on a formula to examine it. And if I persisted and used the named formula in the Conditional Formatting formula … things … just … slowed … down … more.
But interestingly, the performance of the spreadsheet doesn’t appear to be affected when the formula is placed in a cell. I was working in Office 2016. I’ll test it later in Office 365, and report back if I get different results. I actually caused the same freezing problem by using the Evaluate Formula button. Excel slows right down as it creates the array of every cell in column A divided by NOT(ISBLANK). Somehow, however, when the formula is in a cell – Excel manages to handle the calculation much more efficiently. I even checked by making the reference to the formula into a named range. No problems. So, if you are planning to use the AGGREGATE function in this way, you’ll need to actually place it somewhere in the spreadsheet. Oh, and you will need to make that cell into a Named Range or else the Conditional Formatting Formula will not work (that was kinda unexpected).
The Conditional Formatting Formula
The formula to produce this conditional formatting is:
=AND(ROW()<=Last_Row,MOD(ROW(),2)=1)
Last_Row refers to cell $B$2
This checks to see if the current row number is less than or equal to the value in the Last_Row Named Range AND if it is an odd numbered row, if so a blue fill is applied.
This post is originally from 2019. If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca
Here is a scenario: A running total of numbers, updated daily. You want to capture the previous day’s total, as you can see in the picture below.
Looking for the Previous Day’s total in a column of numbers
I’m showing the answer in two steps here, in real life I’d make it into one formula.
The first step is to capture the row number of the previous day’s total. Finding it using the numbers in Column C would be way too complicated. But Column B has the kind of data we can use.
Using the formula =LOOKUP(2,1/(B2:B29<>0),ROW(B:B)) captures the row.
Finding the row number for the previous day’s value.
What the lookup formula is doing is starting by evaluating the numbers from B2:B29 looking for values that aren’t equal to zero.
This creates an array like this: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
The TRUE values will equal 1 and the FALSE values equal 0.
This means when the formula divides 1 by those values, an array looking like this is created:
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
Lookup can’t find 2 in the array, so it settles for the largest value in the array that is less than or equal to lookup_value.The ROW function tells it to return the row number from the array B2:B29.
The second step is to combine the row number with the column number and show the result using =INDIRECT("C"&D2)
And there you have it. A quick way of always finding the previous day (month, year, whatever).
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.
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.
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:
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 Heads waffle chartBombs 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 worksheetand turn off the display of gridlines. That way when you copy the waffle, the gridlines will be invisible.
This post is originally from 2018. If you want help with the newest and classic features in Excel & PowerPoint drop me a line at catharine@mytechgenie.ca
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
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
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 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 itemin 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.
This post is from 2017. The technique is still valid and very useful! If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca
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
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.
This post is originally from 2016. If you want help with the newest and classic features in Excel drop me a line at catharine@mytechgenie.ca
