Cascade Alliance

Technique Tuesday: Tracking Data Using Excel Part 3

We are back with the third and final blog in the series of Technique Tuesday:  Tracking Data Using Excel.  Today we’ll be focusing on how to use different average functions after last weeks tutorial on the addition of All days, weeks and average added to the Excel workbook.

Huge thank you to Rebecca Currie for contributing to our blog these last few weeks!

From Rebecca Currie:

Let’s dig in to the AVERAGEIF detail.

Here is the AVERAGEIF detail for cell C3, which shows the average daily average for the first week of the year for the category Building Materials:

The structure of AVERAGEIF is this:

And this is what that means.

“Range” is the total range you’re working with. In our case, the range is the column in the worksheet All Days that gives the number of the week, from the first day of the year (B2) to the last day of the year (B365). So the range is the worksheet All Days from cell B2 to cell B365, which in Excel formula speak is

‘All Days’!B2:B365

However you want to be able to paste this formula down the page and have it be correct wherever you paste it. In the case of this formula, you want the range to stay exactly the same; it is a fixed range that is the same for every row and column you paste it into. You “lock” this range by including a “$” as part of the range definition, in front of the part of the range you want to stay fixed.

In this case, all parts of the range stay fixed, so you enter

‘All Days’!$B$2:$B$365

[Alternatively, if dealing with the cell ranges letters and numbers feels too confusing, you can set a named cell range. For instance you could name B2:B365 as “weekNumber” and then your formula would be

We can discuss named ranges in a separate post.]

“Criteria” is the thing you are matching on. Cell A3 in Weeks gives the number of the week  that you want the average of from the All Days worksheet. So in cell A3 in Weeks, you enter the week number (e.g., 1, 2, … 52). These are the same numbers that are in column B of All Days, except there are more of them in All Days, one row for every day instead of one row for every week.

The last thing you need to set is the “Range” you want the average from. In this case you are looking for the average of Building Materials, which in All Days is column G. So you set that as G2:G365.

In the case of the range, you want it to always go from 2 to 365 but you want it to adjust as you copy it across the columns —  you want the next column to give you the average of Fabric & Notions in column H, and the next column to give you the average of Crafts in column G, etc. So you put the $ in front of the row numbers, but you leave off in front of the column letter. This means when you copy and paste the formula across the columns, it will adjust to H$2:H$365, I$2:i$365, J$2:J$365, etc.

So that is it! Easy peasy!

You now can see what you daily average is by week from the data you are already entering to track your deposits.

And then at the bottom of weeks I decided to aggregate the quarterly results:

So there you have it.

Happy data tracking!