Select Page

## Meet Aggregate – the function that helps you wrangle hidden rows and subtotals in your formulas..

One issue that can come up in adding up lists with subtotals and hidden rows is that the usual sum() will exclude hidden rows from your calculations. Equally if you have subtotals – you often don’t want those included in your calculations… So...

## How to identify total of top three scores

I was recently asked in a class by a participant who needed to find a way to identify the top three performers in a group. What she wanted to see was the total of their current top three scores. In order to do that I used the Large() function. Then I used Conditional...

## Creating a KPI Dashboard – Part Five – Putting it all together in our dashboard

This is going to be a longer post. I’ve broken down the final creation into a number of pieces. In this first part – you can see how I start the dashboard by applying background colour and borders, creating a dropdown list and starting my calculations. You...

## Creating a KPI Dashboard – Part Four – Putting together our annual stats

You can view Part One , Part Two and Part Three of this series. In this video, we are going to look at how to summarize our year to date (YTD) numbers.  We are using the Iferror() and Indirect() functions to pull in the data from the matching sheets.  You can download...

## Creating a KPI Dashboard – Part Three – Preparing our monthly stats

You can view part one of this tutorial here and part two here. In this tutorial what I want to look at is how to set up formulas to show our monthly stats. We already have our sheets set up and we’ve got a table set up on each one. Next step is to  (a) use group...