Home/ Questions/Q 11680
Next
Answered
computing
  • 12
Asked: 2021-12-08T06:24:36+00:00 In:

Solved Help With Inconsistent Cell Formulas In Tables

  • 12

I’m trying to help a colleague with their regular reporting by removing some maintenance. I’ve changed a chart to make it dynamic by basing its source data on named ranges and using the OFFSET function. That’s all good and working fine.

The chart data (shown below) has three columns and is in turn based on a pivot table. I’ve written a macro to update the pivot table and that works fine. The formulas within the chart data volume column also work fine.

          D              E              F
5       Month         Volume         Average
6     01/05/2016        11        
7     01/06/2016        27
8     01/07/2016        31
9     01/08/2016        27
10    01/09/2016        24
11    01/10/2016        33
12    01/11/2016        23
13    01/12/2016        19
14    01/01/2017        24
15    01/02/2017        46
16    01/03/2017        55
17    01/04/2017        45
18    01/05/2017        50         =AVERAGE(E6:E18)
19    01/06/2017        46         =AVERAGE(E7:E19)
...
30    01/05/2018        19         =AVERAGE(E18:E30)

The formula sequence in the average column continues down to the current month and is intended to give a rolling 12 month average on the chart.

To reduce manual input I changed the chart data into a table so that the user just has to enter the new month at the bottom of the table. The autofilled formula in the Volume column automatically picks up the latest volume for that month without issue.

The problem I’m having is that I was expecting the average formula for 01/05/2018 to autofill in as shown in my example data above, i.e. =AVERAGE(E18:E30), but what it is actually filling in is =AVERAGE(E30:E42).

I’ve Googled the hell out of this but can’t find a fix. It seems to be an issue to do with Inconsistent Calculated Column Formula. I wonder if the blank cells in G6:G17 are contributing, but beyond that I’m stuck.

Any suggestions please?

Share

    1 Answer

    1. Best Answer
      OK, since the problem does seem to be associated with the blank cells (it happens to me too, so I’ll trust you on that) all we need to do is put some formulas in F6:F17.

      I see 2 criteria:

      1 – Don’t start calculating the average until there are 13 values to average (E18)
      2 – Always average the current value with the previous 12 values

      Convert the Table back to a Range, put this in F6 and drag it down to F29, then convert D5:F29 back to a table.

      =IF(ROW()<18,””,AVERAGE(INDIRECT(“E”&ROW;()-12 &”:E”&ROW;())))

      You’ll get blanks until you reach F18. From there on the INDIRECT and ROW functions will create the proper cell references for the AVERAGE function.

      When you enter a Date in D30, the Table Auto Fill feature should do it’s thing.

      If you aren’t familiar with the Evaluate Formula feature on the Formulas tab, check it out. You can single step through the formula as it builds the range.

      message edited by DerbyDad03

      • 0