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

Am Thu, 30 Jan 2020 13:16:16 -0600 schrieb dpb:

Structure of worksheet is:

A |B| C |D| E |F| G |H| I |J| K |L| M
| Year | | Year | |Change | |Budget| |
|Budget | | Actual | |Budget | | Actual | |Y to Y+1| | Y+1 | | Comments

for a number of years with the | representing cell boundaries; the
builder did really put a blank column between each of those holding data. :(

....snip...

Last year I fixed to at least use something like

="Actual 5-yr Average = " &
TEXT(AVERAGE(OFFSET(AT13,0,-8),OFFSET(AT13,0,-12),OFFSET(AT13,0,-15),OFFSET(AT13,0,-19),OFFSET(AT13,0,-23)),"$#,##0")
& ".Minimum = " &
TEXT(MIN(OFFSET(AT13,0,-8),OFFSET(AT13,0,-12),OFFSET(AT13,0,-15),OFFSET(AT13,0,-19),OFFSET(AT13,0,-23)),"$#,##0")

which is even more cryptic but at least does account for the reposition
of the local column excepting it is still referenced to the specific cell.

Is there a way to write something similar to the above that computes the
desired locations given an input number of years over which to look (the
above would be 5, there are a couple that now exist that are 8 instead)?

Seems like there should be some way to write a reference to a set of N
cells spaced every other column or every M cells apart without having to
build a list of N addresses, but I've not come up w/ a simple way to do
so...

if all your cells have the same had the same column distance it would be
easy. But between Y and AD is one more column.
If it would be AC then you could try for average:
=AVERAGE((IF(MOD(COLUMN(M:AC),4)=1,M7:AC7)))

There indeed was a problem in the copy of the sheet above that I pasted
the formula from, Claus, you're correct. It was in fixing that to get
rid of that discrepancy that I was thinking how to write the relative
references.

The idea of MOD() is a good one, however...I'll futz around a little and
see what I can come up with.

--