Variable reference to noncontiguous cells...
 

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. :(

Every year, the four columns equivalent to E-H above for the last year
are duplicated and inserted between H:I in preparation for the next
years exercise and the new years inserted.

Now to the question -- in the comments section are some formulae such as

="Average last 5 years = " & TEXT(AVERAGE(M7,Q7,U7,Y7,AD7),"$#,##0") &
". Min = " & TEXT(MIN(M7,Q7,U7,Y7,AD7),"$#,##0") & ", Max = " &
TEXT(MAX(M7,Q7,U7,Y7,AD7),"$#,##0")

which let one see the range of income/expenses over the previous five
years as well as average. Not every account has the identical comment;
some also have a current balance for that account that is a reference to
another workbook.

The above requires very tedious editing of the formulae to account for
the new year since every column is referenced explicitly; they don't
advance to the next set of five years; they're static.

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...

In MATLAB, I could just write Actual(i1:M:(i1+M*N)) to return the array
elements of interest.

--

Variable reference to noncontiguous cells...
 

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. :(

Every year, the four columns equivalent to E-H above for the last year
are duplicated and inserted between H:I in preparation for the next
years exercise and the new years inserted.

Now to the question -- in the comments section are some formulae such as

="Average last 5 years = " & TEXT(AVERAGE(M7,Q7,U7,Y7,AD7),"$#,##0") &
". Min = " & TEXT(MIN(M7,Q7,U7,Y7,AD7),"$#,##0") & ", Max = " &
TEXT(MAX(M7,Q7,U7,Y7,AD7),"$#,##0")

which let one see the range of income/expenses over the previous five
years as well as average. Not every account has the identical comment;
some also have a current balance for that account that is a reference to
another workbook.

The above requires very tedious editing of the formulae to account for
the new year since every column is referenced explicitly; they don't
advance to the next set of five years; they're static.

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)))


Regards
Claus B.
--
Windows10
Office 2016

Variable reference to noncontiguous cells...
 

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.

--

Variable reference to noncontiguous cells...
 

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

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)))
....

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

It appears Excel evaluates the condition for column M as TRUE then the
condition is such. Isn't an array operation.

I'll keep digging.

--

Variable reference to noncontiguous cells...
 

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


Regards
Claus B.
--
Windows10
Office 2016

Variable reference to noncontiguous cells...
 

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.

--

Variable reference to noncontiguous cells...
 

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.

--

Variable reference to noncontiguous cells...
 

Hi,

Am Thu, 30 Jan 2020 18:56:19 -0600 schrieb dpb:

="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. ")

OFFSET has two more arguments. You can use them to shorten the formula.
If all your cells are filled and you want average the range from column
U to the last column try:
=AVERAGE(IF(MOD(COLUMN(OFFSET(U7,,,,COUNTA(U7:ZZ7) )),4)=1,OFFSET(U7,,,,COUNTA(U7:ZZ7))))


Regards
Claus B.
--
Windows10
Office 2016