Hi Aladin, given the OPs last reply to me
I'd now be dubious about relying on anything other than manually telling it
how many data points to cater for. If the value can be greater than 0 and
less than 0 then I have to believe it is possible to actually be 0. If he's
100% sure it can't then that should do him though
That having been said, in this type of scenario there is usually some kind
of flag on the sheet that denotes a date or something that can be used to
calculate how many date periods and hence data points should be analysed,
even if it's just using something like MATCH/HLOOKUP etc to marry up the
current month and year with the dates that i assume sit on top of this data.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
![Smile :) :)](/styles/default/custom/smilies/smile.gif)
----------------------------------------------------------------------------
Aladin Akyurek said:
Ken,
Maybe...
=AVERAGE(OFFSET(G34,,MATCH(2,1/(G34:Z34<>0))-1,,-5))
is what Geo is looking for.
The formula must be confirmed with control+shift+enter.
Note also that the formula does not check whether there is a sufficent
amount of data points in the range of interest.
<snip>