Remember Me?

#1
January 30th 20, 07:16 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 109
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 = " &

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:Mi1+M*N)) to return the array
elements of interest.

--

#2
January 30th 20, 07:45 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,872
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 = " &

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
#3
January 30th 20, 08:35 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 109
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.

--
#4
January 30th 20, 09:47 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 109
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.

--

#5
January 30th 20, 11:12 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,872
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

#6
January 30th 20, 11:45 PM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 109
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.

--
#7
January 31st 20, 12:56 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 109
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.

--
#8
January 31st 20, 11:40 AM posted to microsoft.public.excel.programming
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,872
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Nick Savage Excel Discussion (Misc queries) 4 April 30th 10 07:08 PM RoadKill Excel Worksheet Functions 5 May 5th 09 09:34 PM goss[_2_] Excel Discussion (Misc queries) 6 November 12th 07 10:26 PM FJ Excel Discussion (Misc queries) 9 December 23rd 06 04:54 PM Vivian Excel Discussion (Misc queries) 2 July 21st 06 01:00 AM

All times are GMT +1. The time now is 05:32 AM.