Average Range Using the first non-zero value

[Brandi]

I am trying to use the Cell formula as the address for a Range in the Average formula.
So, instead of the traditional =Average(B1:B3), I am trying to use the Cell formula for B1 and for B3. Is this not possible?
To make it even more complicated, I am using Match and Index to find the first Non-Zero value. I need to find a 30-60-90-120 average from the first day of production (ie first non-zero value).
For the example, I have narrowed it to a 3 day average from 10 days worth of data, but in reality, I will be using it on 30-60-90-120 day averages on a years' worth of data.

The formula I was trying is:

=AVERAGE((CELL("address",INDEX(B2:K2,MATCH(TRUE,INDEX(B2:K2<>0,),0)))) : (CELL("address",OFFSET(INDEX(B2:K2,MATCH(TRUE,INDEX(B2:K2<>0,),0)),0,2))))

But, it keeps giving me a #VALUE error. Is this just not going to work or do I have something written incorrectly?

Any help will be much appreciated!

Brandi

 

[Hui]

Brandi

You are very close

Your two arguments either side of the : return strings
so simply join them together properly and put them inside an Indirect() to convert them to ranges

=AVERAGE(INDIRECT((CELL("address",INDEX(B2:K2,MATCH(TRUE,INDEX(B2:K2<>0,),0))))&":"&(CELL("address",OFFSET(INDEX(B2:K2,MATCH(TRUE,INDEX(B2:K2<>0,),0)),0,2)))))

 

[NARAYANK991]

Hi Brandi ,

I am not very sure about what exactly you wish to do , but the following formula will give you the average of 3 days of data , starting from the day with the first non-zero value :

=AVERAGE(OFFSET(INDIRECT(ADDRESS(2,MATCH(TRUE,B2:K2>0,0)+1)),,,1,3))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[Brandi]

Thank You Both!!! Hui, yours worked Perfectly! I was so stumped as to why it wasn't working.
Thank You!

 

[Hui]

Doing it from scratch I would use:
=AVERAGE(OFFSET($A2,,MATCH(TRUE,INDEX(B2:K2<>0,),0),1,3))

 

[Brandi]

That is even better, more elegant.

Thank You!