Average Formula 1

[Topcat04]

Hi there

I currently use the following formula to gain an average of the data.

=AVERAGE(BI21INDIRECT(ADDRESS(ROW(),COLUMN(OFFSET($H21,2,MATCH(Summary!$B$1,$I$5:$DI$5,0)))))))

However, this does not give a true reflection of the average qty per week when the data starts at a later date from the beginning week, in this instance BI21 as the weeks with 0 in them bring the average down.

Is there a way to be able to adjust this formula where the starting week is the first entry higher than 0?

For example on this particular data line, the first week where it reported a qty is CC21 - so ideally this is the first week it would take the average from,

Hopefully this can be done and thanks in advance for your help,

 

[SkipVought]

Hi,

So you're defining a range in row 21.

Why does it start in BI21?

What defines the last column in the range?

Is there any data or formula in row 21 beyond this last column in the range?

Please answer all these questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

Yes I am defininig a range in row 21. I am in fact using the same formula for row 6 to 21 and in future weeks will go past row 21 with more data.

It starts in BI21 due to additional data in earlier columns but this is not relevant to the qty.

The last column in the range will be the last week in the year.

There is additional data in row 21 but again not relevant to the average qty sum.

Hope this helps,
Thanks.

 

[SkipVought]

So BI21 can have a ZERO value. Can any other values in your range have ZERO values and if so, do they get averaged?

Is this a forecast of sorts, as you have data thru the end of the year?

Here's a general approch to ignoring ALL ZEROs...
[tt]
=AVERAGEIF(YourRange,">0",YourRange)
[/tt]
or
[tt]
=SUM(YourRange)/COUNTIF(YourRange,">0")
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

Yep BI21 can have a zero value and in fact does in this instance.

It would be rare if qty's produced a 0 after the first non zero, but it is not impossible and yes would be calculated into the average if that was the instance.

The data after the current week (defined by the date in summary $b$1) as the ending point is also all set to 0's, as it pulls from a different sheet that has the current data updated each week which in turn replaces the 0 with a larger qty.

any clearer?
Thanks for your help,

 

[SkipVought]

Try the MATCH() function with a Match Type of 1, looking for a value GREATER THAN.
[tt]
=if(isna(match(0,YourRange,1)),0,match(0,YourRange,1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

Skip

I thought that the expression match(0,YourRange,1) requires sorting YourRange in ascending order?

Is that not the case here?

Just curious

sam

 

[Topcat04]

hi Skip

when you say 'yourRange', do you mean my previous formula.

Sorry for the question, I had help putting the orignial formula together and unfortunately Im confused.

Thanks,

 

[SkipVought]

The lookup range really MUST start with a ZERO.

But since you're using ZERO as the lookup value, it is ONLY the FIRST occurence of a value > ZERO that matters.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

Ah That makes sense

thx Skip

 

[SkipVought]

I take back the MUST start with ZERO, as I have the ISNA() error code to trap when the first value is .GT. ZERO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

yep ...i saw that