Solved Help with inconsistent cell formulas in tables

May 31, 2018 at 04:14:05
Specs: Windows 10
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?



See More: Help with inconsistent cell formulas in tables

Report •

✔ Best Answer
June 4, 2018 at 10:36:44
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



#1
May 31, 2018 at 06:48:04
In your above example the formula is calculating the AVERAGE() of the Date column
and your calculating 13 Rows, not 12, is this supposed to be correct?

MIKE

http://www.skeptic.com/


Report •

#2
May 31, 2018 at 06:49:28
It's a bit difficult for us to replicate your environment because we don't have your pivot tables, macros, etc. nor do we know anything about your work process.

However, simply pasting your example into a worksheet and dragging the formula in G19 down to G30 works fine. I get =AVERAGE(E18:E30) as expected.

Perhaps if you explained how you are autofilling the Average formula I could try something different.

I am a bit confused about something else. Your AVERAGE formula references Column E. Column E contains dates. What is the purpose of averaging a series of Dates?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#3
May 31, 2018 at 07:54:39
Thanks both for responding.

With regard to mmcconaghy's point, I noticed that too but haven't had a chance to confer with my colleague yet so still need to find out.

For DerbyDad03 - I'll answer your confusion point first. That's just me being dumb when posting this question. The columns in reality are D, E and F which is why I've quoted the formula correctly, but recreated the sheet layout incorrectly... Nice spot though! :-) I'll go back and edit that after posting this response so anyone reading this later on doesn't get confused.

I'll expand on the context if that helps. Sorry for the length.

The workbook actually has 10 tabs. The first is "Clean Data" and is populated by a user pasting in data from an exported sharepoint site. This is a Table.

The second tab is called "National" and is the one I'm referring to in this question. The remaining tabs are just regional variations of the national tab and replicate the layout described for the National tab described below.

The original workbook when I received it from my colleague has a pivot table on the National tab which is based upon a "Month" column in the "Clean Data" tab. The pivot in columns A:B of the National tab just counts how many times (let's call them items) have been recorded within each given month.

The workbook then had the data referenced in my original posting in columns D:F starting from row 5 (including the column headers) . The first date in D6 is 01/05/2016 with a corresponding volume in E6 gathered from the pivot table using the formula =IF(ISERROR(VLOOKUP(D6,$A$6:$B$37,2,0)),0,(VLOOKUP(D6,$A$6:$B$37,2,0))). The subsequent date values progress down column D as referenced above, all the way down to D29 which has 01/04/2018. The formula quoted here is copied all the way down in column E to E29.

In column F, there are no values until you get to F18 where you fist see the formula =AVERAGE(E6:E18). The formula progresses down as referenced in my first post all the way to F29.

Next to the data in columns D:F there is a column chart based upon the data in columns D:F. There are two data series - one for the volumes in column E, and one for the averages in column F. The averages series is a line chart type over the volumes series which is a column chart type.

Each month my colleague's team export data out of sharepoint, copy and paste it onto the "Clean Data" tab, insert a "Month" column in Clean Data and populate it with date values that are the first of the month of which the item was created (shown in a "Date Created" column on the Clean Data tab. They then have to refresh the pivot table on the "National" tab to pick up the new month's data. Then they enter the latest month into a new row at the bottom of the data series contained in columns D:F, and drag down the formulas in columns E:F. They then have to update the data source for the chart and end up copying the chart into a slide deck.

I've written a macro (still a work in progress as I was going to post another question here about not relying on hard coded data ranges as I suspect further columns will be added into the "Clean Data" tab at some point that will mess up the macro) which automates everything from the column insertion through to the refresh of the pivot table. The code is here:

Sub PrepReport()

Dim Table As ListObject
Dim ws As Worksheet
Dim pt As PivotTable
Dim LastRow As Long


Application.ScreenUpdating = False

'Name the table as "Pivot Source", insert a new column and name it "Month"
Set Table = Worksheets("Clean Data").ListObjects("PivotSource")
Table.ListColumns.Add 12
Table.HeaderRowRange(12) = "Month"

'Set the values of "Month" column to the first day of the month for each value in the "Date Created" _
column and get rid of the time stamp
Range("L2").Formula = "=INT(K2-DAY(K2)+1)"
LastRow = Range("L65536").End(xlUp).Row
'Set the format of the "Month" column
Range("L2:L" & LastRow).NumberFormat = "dd/mm/yyyy"

'Refresh all the pivot tables in the workbook
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.Refresh

Next
Next

End Sub

I was then thinking about coding to update the graph automatically but read that this can be quite complex and came across dynamic charts. This is why I turned the data in columns D:F into a table and based the chart on named ranges, so that all the user would have to do is enter a new month at the bottom of column D, the table would autofill the formulas in columns E:F and the chart would update automatically. That was theory at least until I hit this snag with the averages.


Report •

Related Solutions

#4
May 31, 2018 at 09:50:46
OK, that was a lot and I'm not sure that much of it has any impact on the original issue: The incorrect cell references in Column F. I'll get back to that later.

There were a couple of unrelated items that jumped out at me:

1 - You are using this formula:

=IF(ISERROR(VLOOKUP(D6,$A$6:$B$37,2,0)),0,(VLOOKUP(D6,$A$6:$B$37,2,0)))

You might want to try the more "modern" way to get the same result:

=IFERROR(VLOOKUP(D6,$A$6:$B$37,2,0),0)

2 - You are using this code:

LastRow = Range("L65536").End(xlUp).Row

Again, you might want to update that syntax:

LastRow = Range("L" & Rows.Count).End(xlUp).Row

Beginning with Excel 2007, there are more than 1 million rows available in a spreadsheet. By using 65536 you run the risk of missing some data. Maybe not in this spreadsheet, but it's best to be safe and consistent. By using Rows.Count instead of a fixed value, that instruction becomes compatible with all versions of Excel, old and current and probably future.

OK, so back to your question:

You originally showed this as your desired output. (I'm not worried about the Column letters at this point, you've addressed that)

18    01/05/2017        50         =AVERAGE(E6:E18)
19    01/06/2017        46         =AVERAGE(E7:E19)
...
30    01/05/2018        19         =AVERAGE(E18:E30) <------ Desired

But you say that are actually getting this:

18    01/05/2017        50         =AVERAGE(E6:E18)
19    01/06/2017        46         =AVERAGE(E7:E19)
...
30    01/05/2018        19         =AVERAGE(E30:E42) <------ Wrong!

When I asked for clarification, you said:

"The subsequent date values progress down column D as referenced above, all the way down to D29 which has 01/04/2018."

And you also said:

"Then they enter the latest month into a new row at the bottom of the data series contained in columns D:F, and drag down the formulas in columns E:F.

Have you looked at the formulas in the rows you haven't shared with us, i.e. Rows 20:29?

The reason I ask is because you say that they drag the formula in Column F down and that you are getting the wrong cell references in Row 30, but you don't say if Row 29 is correct, or Row 28, or any Rows after the last correct reference in Row 19.

If all that the users are doing is manually dragging the formula down, then any error in the cells referenced in Row 30 have to be based on a cell reference somewhere above it. In other words, something between Row 19 and Row 30 must have gone astray because Row 19 is correct and Row 30 isn't.

The only other thing that I can think of is that you aren't actually autofilling Row 29 to Row 30, but are actually dragging the formula into Row 42 because Rows 30:41 are hidden. Dragging from Row 29 to Row 42 when Rows 30:41 are hidden would autofill 30:41 even though they are hidden, resulting in Rows 30:42 being referenced in the next visible Row, which is 42. I know that that is a stretch (you'd know if the rows were hidden, wouldn't you?) but the root cause has to be one of 3 things:

1 - An incorrect reference somewhere in Rows 20:29
2 - Hidden rows
3 - A corrupt worksheet

I listed those in the order that I think is most probable. On the other hand, I could be totally misunderstanding what is going on. ;-)


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#5
June 1, 2018 at 18:14:56
Thank you for marking the thread as Solved, but it would nice to know what the cause of the issue was.

Since these threads are saved it in the archives for other to use when they have similar issues, having the final cause known would be very helpful to others. Please share.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
June 4, 2018 at 00:29:12
Ah ok. Maybe I should remove the solved marker then because I still haven't found what is causing the issue. But in working through this, I've found so many other things I'd need to attend to in order to complete this project for my colleague that I simply don't have enough time alongside my day job to accomplish it.

What I can say is that there are no hidden rows, and the worksheet isn't corrupted. Having done more reading on google about similar problems experienced by others with Excel tables, I'm convinced (though don't have the evidence) that the problem with the Average formula is caused by the blank rows in F6:F17. If you drag the formula down into a new blank row at the bottom of the table, the formula works fine as Excel knows what you're doing. But if you start typing in a new blank row at the bottom of the table, the formula in column E copies down perfectly, because column E's formula is filled from top to bottom. But the formula in column F doesn't auto fill and either stays blank (usually) or the error I described above occurs. I assume because Excel is confused by a series of blank rows, followed by a series of rows with a formula and can't work out what to do so does nothing or gets it wrong.

So for others, I'd say if you're using a table, try to avoid having blank rows if you need a formula to auto fill. Unless someone else knows something I don't.

Finally, I can't seem to edit my first post now, so to be clear the columns in my first post should be labelled D to F, NOT E to G. Sorry for the confusion.

Thanks for the help already given though. Much appreciated.

message edited by ScottV


Report •

#7
June 4, 2018 at 05:55:01
I am still confused by your use of drag vs. auto fill . You seem to be contradicting yourself. Earlier you said that the users were auto filling by dragging but now you say that dragging works but auto filling doesn't. Let's recap:

Here is what you said in Response #6:

If you drag the formula down into a new blank row at the bottom of the table, the formula works fine as Excel knows what you're doing. But if you start typing in a new blank row at the bottom of the table, the formula in column E copies down perfectly, because column E's formula is filled from top to bottom. But the formula in column F doesn't auto fill and either stays blank (usually) or the error I described above occurs.

That indicates to me that the auto filling is not being done by dragging the formula down.

Now let's go back to the early part of this thread.

In Response #2, I said that I was dragging the formulas down and not having any problem. I then asked: "Perhaps if you explained how you are auto filling the Average formula I could try something different."

In Response #3 you answered "...they enter the latest month into a new row at the bottom of the data series contained in columns D:F, and drag down the formulas in columns E:F."

That indicated to me that the users were dragging to auto fill but now you are saying that dragging works but auto filling doesn't.

That take me right back to my original question. If the users aren't dragging to auto fill (which you said they were) then how does the auto filling occur?


message edited by DerbyDad03


Report •

#8
June 4, 2018 at 07:19:47
Sorry If I've confused you. To recap:

Currently (as is)
Remember I said that I was trying to help a colleague remove a lot of the manual work required in their monthly reporting. In the existing spreadsheet users have a data range (in columns D to F) where they enter a new date at the bottom of the range then drag formulas down into the new row which works fine.

Automation changes (what I want to achieve)
What I was trying to achieve for them is to get rid of as much manual input as possible. As part of that I've been experimenting with changing the data range into a table. Step 1 was to manually type in a new row in column D, with the expectation that Excel would then auto fill the formulas in columns E and F. This is where I've hit the issue with the averaging.

If this all worked I was intending to then move on to step 2 to look at including in the macro code some way of putting in the latest date at the bottom of the table without any user input at all after running the macro. But until I can solve this issue with the averaging formula not auto filling correctly, they'll have to stick with their current manual method where they have to drag down.

Hopefully that makes it clearer?


Report •

#9
June 4, 2018 at 08:18:35
It won't be any clearer until you explain how you are doing this:

"Step 1 was to manually type in a new row in column D, with the expectation that Excel would then auto fill the formulas in columns E and F. This is where I've hit the issue with the averaging."

How exactly are you accomplishing the "auto fill" of Columns E:F when the user enters a value in Column D? Since you say that Column E works fine ("if you start typing in a new blank row at the bottom of the table, the formula in column E copies down perfectly") then you must be doing something that "auto fills" E:F when the data is entered. Are you using an Event macro that fires on the change in Column D? If not, what is causing the "auto fill" to occur?

message edited by DerbyDad03


Report •

#10
June 4, 2018 at 08:47:06
I don't know the technical details behind it, but with an Excel table, if you enter new data into the row immediately below the table, once you hit enter, Excel determines that a new row is being created. If there are formulas in the row/s above, it will auto fill those formulas down into the new row just created.

In this case, as part of my efforts to automate, I converted the range D5:F29 into a table and to do some testing, manually entered data (a date) into D30, i.e. the cell immediately below the table. Excel automatically extended the table to encapsulate this new row, and correctly auto filled the formula (the IFERROR formula you recommended in post #4) in E30, but didn't correctly auto fill the AVERAGE formula into F30.


Report •

#11
June 4, 2018 at 09:04:00
OK, now I've got it. I don't do a lot of work with tables so that feature didn't just jump right out at me, especially since you mentioned the "drag" process in other posts. My confusion is now cleared up.

Let me see what I can figure out.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#12
June 4, 2018 at 10:36:44
✔ 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


Report •

#13
June 5, 2018 at 01:01:21
What a star you are. Works perfectly.

I'm going to end up having lots of questions with this project I think so I guess I'll just post them as they arise. As I mentioned in my 'essay' in post #3 I want to work on changing the macro code to avoid using hard-coded cell references. I'll post that as a separate question if I can't work it out as I'm sure it would be useful for other VBA newbies like me.

Thanks as always.


Report •

#14
June 5, 2018 at 03:12:42
I'm glad I could help.

Even though you are working on a single project, it's best to ask "unrelated" questions in their own thread with a relevant subject line. That makes the specific issue easier to search for.

Feel free to reference project related threads by including links to other threads in your posts if you think the background is needed.

Good luck!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question