Average of cells to end cell of row which is always growing

Hi Snout,

You can reference a whole row like this:

Formula is in a Header Column.

Formula in B2 is AVERAGEIF(2:2,">0")

To get the 2:2, click on the column number (2).

As you add new columns, the formula will automatically expand.

Regards,

Ian.

That's sounds like an "Excelsy" format. Is there a reason you need such a wide table? (Financial reports, perhaps, where the convention is to have years or quarters across the top?)

If you're going to be adding new data it often works better to set things up so you enter it in new rows rather than new columns.

You could designate the first five rows as header rows (equivalent to your columns A through E) with the rest body rows (your F through BX and expanding).

Then add a Footer Row and put your formula in it. If your data is in, say, column B, then you formula is simply

=AVERAGEIF(B,"<>0")/1000.

Advantages:

• The formula is dead simple.
• New data is automatically included as you add it.
• "Tall" tables are easier to work with than "wide" tables, which can get unwieldy as you add lots of columns.

Bonus:If you happen to have formulas in some of the other columns then Numbers automatically copies these down into new rows when you add one by selecting the last row and hitting <return>.

If you want to experiment with this type of approach then you can make a copy of your table (click the concentric rings to its upper left and option-drag onto the canvas). Then click somewhere in the copy and choose Table > Transpose Rows and Columns from the menu.

SG

There are a couple of ways of approaching this, depending on your appetite for creativity, as well as the specifics of your table. :)

Assuming the data fills your table, you can use the COLUMNS() function to find the number of columns in the table. This will increase each time you add a new column.

Note: this technique may fail if your table exceeds the number of columns, so try it and if we need to rework it we will... I always prefer the simple options first :)

So COLUMNS(Table 1::43) will return the number of columns in that row.

You can pass this number into INDIRECT() to calculate a reference. For example:

=INDIRECT("Table 1::R43C"&COLUMNS(Table 1::43), FALSE)

will return a reference to the last cell in row 43 (the FALSE at the end tells Numbers you're using R1C1 format rather than than the more common A1-style, and this saves us from having to convert the column number to an index (e.g. 'BX')

INDIRECT() works by taking a textual description of a cell (or range) and converting it into references that other functions can work with.

You can extend the INDIRECT() function to return a range instead of a single cell, like:

INDIRECT("table 1::R43C6:R43C"&COLUMNS(Table 1::43:43),FALSE)

So now we have a reference that points to Row 43, Column 6 (or F) through the last column in row 43

We can pass this into your existing AVERAGEIF() function, thus:

=AVERAGEIF(INDIRECT("table 1::R43C6:R43C"&COLUMNS(Table 1::43:43),FALSE),"<>0")

which looks like this:

Now you'll get an average of all the non-zero cells in row 43, from column F to the end of the table.

Hi Snout,

Is there a real need to have Columns A, B, C, D and E attached to the left of your data table? What do those columns contain? What is there purpose?

More information on your overall aim may get our creative juices flowing. A screen shot of a few columns of your data table will help us visualise the layout of your document.

Perhaps this will kick off some discussion. An idea using the Numbers approach of separate tables, each with a purpose:

No formulas in the Data table.

Summary table allows for several statistics. Also, the summary table can be moved to another Sheet as a Presentation.

Happy Numbering!

Ian.

Hi Snout,

Thanks for the screen shot. That brought on an "Aha" moment. Columns A to E are Header Columns (the clue is that they are frozen and other Columns (up to BM) have been scrolled "behind" them. Good. (BTW, did you hide Column A?) Numbers allows up to 5 Header Columns, so you are up to the max.

One complication that I see is the merged cells (e.g. 4, 5, 6, 7). I don't know how that will affect formulas for Avg Tourn and Avg event.

But we are progressing!

Regards,

Ian.

Hi Snout,

Will this work for you?

From your screen shot, there are up to four scores for each player on each date.

I saw the need to identify the four "Rounds" then calculate Avg Tourn and Avg Event for each "Round", then sum them in a Summary table. Does that make sense?

In the Data table I took out the merged cells (they could cause glitches). I made each row a unique record (Player name in every row). That makes it easier to SUMIF over each Player and Round. Five Header Rows - as five is the max that Numbers allows per table, that's another reason for the Summary table.

What is in your hidden Column A?

What is the purpose of the numbers in my Column A? I had to use your Column A.

If you think that this layout will work for you, let me know and I will insert the "whole row" formulas for Avg Tourn and Avg Event.

Regards,

Ian.

Looks like you have it well in hand, so I won't interfere further. Happy Numbering!

> You can reference a whole row like this:

Except that does not solve the OP's problem.

Yes, you can reference an entire row (or column) in this way, but the OP wants a subset of the cells in that row.

>AVERAGEIF(F43:BX43,"<>0",avg-values)÷1000

There is no way to reference the entire row MINUS the first 5 columns (A-E) using this method. You have to build a reference to the cells you DO want. My INDIRECT() approach is one way of doing that.

Hi again, I tried to edit that reply but

Bugga! New reply.

My edit was: "The good news is that we can use "whole row" formulas (as in my 2:2 idea in my earlier replies).

Ian.