Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 30th 20, 07:16 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 89
Default 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:Mi1+M*N)) to return the array
elements of interest.

--


  #2   Report Post  
Old January 30th 20, 07:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,797
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
  #3   Report Post  
Old January 30th 20, 08:35 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 89
Default 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   Report Post  
Old January 30th 20, 09:47 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 89
Default 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   Report Post  
Old January 30th 20, 11:12 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,797
Default 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   Report Post  
Old January 30th 20, 11:45 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 89
Default 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   Report Post  
Old January 31st 20, 12:56 AM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 89
Default 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   Report Post  
Old January 31st 20, 11:40 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,797
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017