On 1/30/2020 5:45 PM, dpb wrote:
On 1/30/2020 5:12 PM, Claus Busch wrote:
Hi,

Am Thu, 30 Jan 2020 15:47:44 -0600 schrieb dpb:

This averages all the cells between MC:AC7, not just those for which
MOD(...,4)=1.

no, I tested it and it only averages the cell in M, Q, U, Y, AC

...

I forgot to enter as array formula...my bad.Â* I'm pretty-much ignorant
of Excel so dumb mistakes are the norm...

Thanks, it does work if done correctly.Â* :)

That's a big help.Â* If now translate to use the OFFSET for origin so is
dynamic, will be all set.

Well, it's a mouthful and doesn't have the nicety of a variable number
of years over which to summarize, but:

="Average last 5 years
="&TEXT(AVERAGE(IF(MOD(COLUMN(OFFSET(AS7,0,-24):OFFSET(AS7,0,-8)),4)=1,OFFSET(AS7,0,-24):OFFSET(AS7,0,-8))),"
$#,##0. ") &"Range =
"&TEXT(MIN(IF(MOD(COLUMN(OFFSET(AS7,0,-24):OFFSET(AS7,0,-8)),4)=1,OFFSET(AS7,0,-24):OFFSET(AS7,0,-8))),"
$#,##0 ")&"-"
&TEXT(MAX(IF(MOD(COLUMN(OFFSET(AS7,0,-24):OFFSET(AS7,0,-8)),4)=1,OFFSET(AS7,0,-24):OFFSET(AS7,0,-8))),"
$#,##0. ")

does the trick.

Thanks; the MOD() idea was the key, I was trying to figure out a way to
emulate MATLAB colon indexing that got me into the quagmire of trying to
calculate locations.

--