Home 
Search 
Today's Posts 
#1




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 EH 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 5yr 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




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 EH 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 5yr 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




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 5yr 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




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




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




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




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




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) 