View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default 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