Trouble with cell references in AVERAGE function

[DashboardNovice]

I am receiving a #VALUE! error. Here is my formula:

=AVERAGE(I11&":"&"I"&SUM(ROW(I11),(Sheet5!M5-1)))

With respect to the second cell in the range it will begin with column I, but I want the row value to be able to adjust based on the value entered on Sheet5, cell M5.

When I step through with the formula evaluator, first cell I11 is being evaluated and turning into a number, then the remainder evaluates as a cell reference which is what I want. So I am ending up with AVERAGE("112.07:I15").

I guess my problem is that I have to find a way to ensure that the first cell, i.e. I11 doesn't evaluate into a number. Is that it?

I suspect that the correct solution could involve the use of the ADDRESS or INDIRECT function, which I would like to avoid if I can.

 

[NARAYANK991]

Hi ,

Instead of giving your formula , can you explain which cells you would like to average , based on the input value entered in Sheet5!M5 ?

What you want done can most probably be done using the OFFSET function.

Narayan

 

[DashboardNovice]

Hi ,

Instead of giving your formula , can you explain which cells you would like to average , based on the input value entered in Sheet5!M5 ?

What you want done can most probably be done using the OFFSET function.

Narayan

I have my file uploaded. Basically, I am allowing a user to specify a moving average in cell M5 of the first tab.
The user can enter a value or even change. So this formula will accommodate a change in the value submitted.

On the tab "raw data" see cell L11 in yellow. This is where my formula is. Therefore, in order to calculate a moving average with the AVERAGE function, the row number of the 2nd cell address needs to be able to change.

 

[NARAYANK991]

Hi ,

Something like this ?

=AVERAGE(OFFSET($I$11,,,Sheet5!M5-1))

Narayan

 

[DashboardNovice]

Hi ,

Something like this ?

=AVERAGE(OFFSET($I$11,,,Sheet5!M5-1))

Narayan

Brilliant solution and much simpler than what I had in mind. I had to remove the -1 to get the result I wanted.

However, now another issue has come up. Recently I posted a question about how to ensure that the AVERAGE function will return an error if a cell in the range to average has a text value. You mentioned that the solution is to multiply by 1, and enter as an array. But this time may data is sorted the other way.

Using the current value of 5 in cell M5 on the first sheet (meaning a 5 day moving average), when I copy my moving average formula down, the last cell that should return a result is L72 in orange. The last four cells below in red need to return an #N/A error because fewer than 5 data samples are available. For example, in the case of cell L74, the AVERAGE function would evaluate 3 numbers and 2 blanks (I74:I78). Because this doesn't contain 5 numbers it is invalid as a 5 day moving average. So at this point, I'm trying to find a way for the AVERAGE function to return an #N/A error whenever a cell in the range to be averaged contains at least one blank cell.

I tried the method where you multiply by 1 and it didn't work. Is there a simple way to ensure that AVERAGE will return an #N/A error, when the AVERAGE function encounters a blank cell?

Please see this new version of the uploaded file.

 

[NARAYANK991]

Hi ,

Can you not use this ?

=IF(COUNT(OFFSET(I11,,,Sheet5!$M$5))<5,NA(),AVERAGE(OFFSET(I11,,,Sheet5!$M$5)))

Narayan

 

[DashboardNovice]

Hi ,

Can you not use this ?

=IF(COUNT(OFFSET(I11,,,Sheet5!$M$5))<5,NA(),AVERAGE(OFFSET(I11,,,Sheet5!$M$5)))

Narayan

Another brilliant solution. Thank you very much. You are awesome.

So basically look at the range to average and apply the count function to it to determine the number of cells that do contain numbers (and therefore not blanks), and that number must be equal to the number of days we are averaging for. Why didn't I think of that? Thank you again.