Insert a row and update formula

[MalcolmWalker]

In a worksheet, in a column, cells C92 to C94 (inclusive) have data that consists of recorded scores. In cell C95 is the formula =AVERAGE(C2:C94) which displays the required result. (Columns A and B record when (date) and where (location).)
To add a further score by inserting a row above row 95 the formula is unchanged. If I insert a row anywhere between the rows containing C2 and C94 the formula will automatically update to =AVERAGE(C2:C95) and the formula will be in cell C96. Again the result is as required but the scores are no longer chronological. A sort, of all the data, based on column A will resolve that issue but it is rather inelegant and subject to user errors to have to do a sort each time the list is added to.

How can I insert a row after C94 and have Excel update the formula automatically? Or, other than sort as mentioned above, do I have to update the formula manually or possibly use a macro?

Assistance, even an elegant solution will be something to anticipate with pleasure!

[BobH]

Howdy Malcolm!

From your post, I can't tell if the problem is maintaining chronological order in column A or updating the formula to the new end-of-range value or both.

A simple solution would be to give the entries in column C a range name. Select the range C2 thru C94 and click the Formulas tab then select Define Name. A wizard appears asking for a name. You can use any name you want but to keep it consistent you might choose "Score". Type that in the wizard box and click OK. Change your formula to "=AVG(Score)" (without the quotation marks) and Bob's your uncle.

I would also suggest you use column D row 2 for you formula and a title in D1 "Average"

Does that solve the problem or do we still have an issue of date chronology?

[Rudi]

Hi Malcolm,

Out of interest, what version of Excel are you on?

In Excel 2013 (and in Excel 2010 AFAIK), if you insert a row directly above the function, it will automatically update the references to include the new inserted row. See the screenshots to confirm. In the screenshots I inserted a new row 7 and added a value of 12. The function references automatically updated to B2:B7 without any further intervention from my side. I am not sure if this was a new feature in Excel 2010 or if it existed before this version, but I'm just interested to confirm as inserting a row just above the function should not pose the issue you refer to.

I appreciate any feedback. TX.

SC001-vert.jpg

ADDENDUM:

Oh, BTW: Another very useful solution would be to convert your list into a structured table and use the built in table features to maintain your average. In the screenshot below, I converted the sample into a table, and using the Table Design contextual ribbon, I selected the option for "Total Row". This puts an automatic total row below the last row of data and you can choose the aggregate it in any way you want. The table will of course maintain this automatically as you add or remove rows within the table (in any position).

SC003.jpg

[MalcolmWalker]

For BobH and Rudi - Thank you for your responses; between you I have quite a lot to think about. I had all but given up in this matter and as a last resort decided to ask in the Lounge. I hadn't expected such erudite responses and I should have known better! But an answer for Bob and some further information for Rudi next.

Bob, the insertion of a row is presently a user function and the onus is on the user to maintain the chronology. So the emphasis is principally on the average score. I'll implement your suggestions later and update thereafter. My first post was somewhat simplified in that the actual formula for the average is =AVERAGE(C2:C30,C32:C40,C42:C94) and as such may complicate a satisfactory solution.

Rudi, I use Excel 2007 but the original worksheet is kept in Google Sheets (on-line) and I have been given permission to access and edit. To what extent Google Sheets and any versions of Excel are comparable I do not know. In both Google Sheets and Excel 2007 I haven't achieved the behaviour, in both Excel 2013 and 2010, you report. Incidentally copying the worksheet from Google Sheets into Excel 2007 didn't bring through the average formula or other formulas in the copy/paste process. Only the numbers resulting from the formulas were copied. I assume I have to use Paste Special, which is available in Google Sheets, to get the formulas as well.

[Rudi]

Hi Malcolm,

The auto update of the formulas (as I explained in my reply above) is unfortunately only applicable to functions that have a fully contiguous range. Since your average function has many small ranges specified in the formula (separated with commas), the auto reference update will not work. With regards to Google Sheets, I have little experience using and working on them, but I did find out that functions that are compatible with Excel will maintain their calculable nature (IOW, they will not convert to fixed values). Functions that are not compatible between the two apps will show #Name errors after the export.

Details of this info from this source: How to save a google spreadsheet as excel with Formulas

[HansV]

Would the following be feasible?
Move the average formula from C95 to C96 (leaving C95 blank), and change it to

=AVERAGE(C2:C30,C32:C40,C42:C95)

The result will remain the same since the AVERAGE function ignores blank and text values.
But if the user now inserts a new row below the data, i.e. in row 95, the formula will automatically be adjusted to

=AVERAGE(C2:C30,C32:C40,C42:C96)

[BobH]

Or, perhaps, have multiple named ranges with a formula of =AVG(Range1+Range2+Range3). I think, and I'll defer to Hans, Rudi and others here, that you can define your range names with a space (or spaces) at the end so that new entries will be included. I seem to have forgotten the specifics of how range names work but I believe that appending or inserting a row or column into a named range keeps the range intact without having to redefine its limits.

[Rudi]

Hi Bob,

I very rarely use range names (even though they have a lot of value in Excel). AFAIK, named ranges respond in the same way as standard range references, but I say this tongue in cheek as I have not used them in a long time (barring a few dynamic named range references). I guess that if Malcolm experiments with your suggestion, he can confirm if my assumptions are right or wrong.

[HansV]

Static named ranges are really static: if you define a name that refers to the contiguous range Sheet1!$A$1:$A$5, and then insert a new row in row 6 (immediately below the named range), that name will still refer to Sheet1!$A$1:$A$5, i.e. the new name is not included.

Malcolm's setup doesn't really lend itself to using dynamic ranges...

[BobH]

I yield to superior intellect, knowledge and experience.

[Rudi]

Bob, we ALL yield to the valued posts by Hans, but please continue to post replies as they always add to the value and learning aspects of these threads. Supplying answers from your experiences and understanding are just as valuable as any technically correct answer.

[MalcolmWalker]

Gentlemen, I very much appreciate your further contributions to this topic.

Rudi please note I had tested both Google Sheets and Excel 2007 with a contiguous range of cells in the average formula without, in both instances, success in the formula updating. It would be good to have a Lounger with Excel 2007 to check my result.

Otherwise, I think Hans' solution will suit the user as it entails actions that he is expecting, is comfortable with and are well within his and my competence. If accepted I will not have to delve into all the possibilities raised. But to become aware of them is illuminating!

Thank you all for your very interesting responses and discussions.

Malcolm