Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable reference to add cells | Excel Discussion (Misc queries) | |||
COUNTIF on NonContiguous Cells? | Excel Worksheet Functions | |||
average of 12 noncontiguous cells | Excel Discussion (Misc queries) | |||
Adding noncontiguous cells | Excel Discussion (Misc queries) | |||
noncontiguous cells | Excel Discussion (Misc queries) |