PDA

View Full Version : [SOLVED] Formula to calculate AVERAGE but only to last used row in a grid.



PAB
01-25-2017, 08:13 AM
Good afternoon,

I have a grid of cells C16:O25 where there are only values and empty cells, no formulas.
I want to calculate the average for each column within the grid.
I want it to calculate the average to include 0 for empty cells but only calculate the average down to the last used row from C:O.
As you can see from the example, column C finishes in row 20, BUT the actual last row is 21.
I would like it to go down to row 21 for all the calculations please.
The last row in future might be 18 or 22 or 25 for example.

The correct results are shown in cells C26:O26 but I obviously want them in cells C15:O15 please.

I have tried using the formula...


=AVERAGE(0+C16:C25)

...but this calculates it down to row 25, regardless if the last row was different.

I have tried using INDEX/MATCH but without any luck.

Any help will be appreciated.

Thanks in advance.

PAB
01-25-2017, 08:38 AM
Not to worry,

I used a helper column from B16:B25 with the formulas:


=IF(SUM(C16:O16)>0,1,"")
=IF(SUM(C17:O17)>0,B16+1,"")

...etc down to cell B25.

Then formulas:


=IFERROR(SUM(C16:C25)/MAX($B16:$B25),0)

...copied across from cells C15:O15.

Thanks to all those that looked though.

EDIT: Formula change to include if there was no data that it would not invoke a #DIV/0! error.

p45cal
01-25-2017, 11:20 AM
in cell C15, array enter (Ctrl+Shift+Enter, not just Enter):
=SUM(C$16:C$25)/(MAX(IF(LEN($C$16:$O$25)>0,ROW($C$16:$O$25)))-15)
and copy across.

Or
=SUM(C$16:C$25)/(MAX(IF(ISNUMBER($C$16:$O$25),ROW($C$16:$O$25)))-15)
if you want it to be insensitive to text in the grid.

PAB
01-25-2017, 12:19 PM
Brilliant, thanks very much p45cal, it is appreciated.