{"id":11680,"date":"2021-12-08T06:24:36","date_gmt":"2021-12-08T06:24:36","guid":{"rendered":"https:\/\/lgildv5i97.onrocket.site\/answers\/?post_type=question&#038;p=11680"},"modified":"2021-12-08T06:24:57","modified_gmt":"2021-12-08T06:24:57","slug":"solved-help-with-inconsistent-cell-formulas-in-tables","status":"publish","type":"question","link":"https:\/\/computing.net\/answers\/office\/help-with-inconsistent-cell-formulas-in-tables\/21304.html","title":{"rendered":"Solved Help With Inconsistent Cell Formulas In Tables"},"content":{"rendered":"<p>I&#8217;m trying to help a colleague with their regular reporting by removing some maintenance. I&#8217;ve changed a chart to make it dynamic by basing its source data on named ranges and using the OFFSET function. That&#8217;s all good and working fine.<\/p>\n<p>The chart data (shown below) has three columns and is in turn based on a pivot table. I&#8217;ve written a macro to update the pivot table and that works fine. The formulas within the chart data volume column also work fine.<\/p>\n<pre>          D              E              F\r\n5       Month         Volume         Average\r\n6     01\/05\/2016        11        \r\n7     01\/06\/2016        27\r\n8     01\/07\/2016        31\r\n9     01\/08\/2016        27\r\n10    01\/09\/2016        24\r\n11    01\/10\/2016        33\r\n12    01\/11\/2016        23\r\n13    01\/12\/2016        19\r\n14    01\/01\/2017        24\r\n15    01\/02\/2017        46\r\n16    01\/03\/2017        55\r\n17    01\/04\/2017        45\r\n18    01\/05\/2017        50         =AVERAGE(E6:E18)\r\n19    01\/06\/2017        46         =AVERAGE(E7:E19)\r\n...\r\n30    01\/05\/2018        19         =AVERAGE(E18:E30)<\/pre>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>The problem I&#8217;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).<\/p>\n<p>I&#8217;ve Googled the hell out of this but can&#8217;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&#8217;m stuck.<\/p>\n<p>Any suggestions please?<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":8},"question-category":[53],"question_tags":[],"class_list":["post-11680","question","type-question","status-publish","hentry","question-category-office"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/11680","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=11680"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=11680"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=11680"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=11680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}