Changing an array formula for a different cell range

Copper Contributor

If anyone can help - I have a large formula that works for the cell intended. I need the same formula in different cells, but with a different look up range. Editing the formula range does not change the value. I have tried Control-Shift-Enter and it does nothing. Please help! The formula is :

=SUM(INDIRECT(ADDRESS(MATCH(A4,Transpose!$A$4:$A$55,0)-3,19,1,TRUE,"Transpose")):INDEX(INDIRECT(ADDRESS(MATCH(A4,Transpose!$A$4:$A$55,0)-3,19,1,TRUE,"Transpose")):INDIRECT(ADDRESS(MATCH(A4,Transpose!$A$4:$A$55,0)+3,19,1,TRUE,"Transpose")),7))

Changing the range (A4:A55) inside the formula doesn't change the value.

3 Replies
so if I'm reading the formula correctly you are doing a 7 point average around the value of cell A4 in that range. Changing the range A4:A55 may not affect it, especially if you just shift it up or down a few rows and A4 is found towards the middle. honestly the formula seems overly complicated and having unneeded calculations and components. You are probably better off sharing the sheet (personal/private/confidential info removed) and telling us what you need and we can probably set you up with something better.

@mtarler 

Thank you for your help. I am attaching an example. I need the journal entries to autofill based on the date of the journal entry, with the sum of the date of the entry and prior 6 months. The data is in the Summary tab, but I have also transposed it (Transpose tab) - the range can come from either tab. However, each line on the JE refers to a different range, which I have color coded the first few as an example. !st attachment is JE I need formula, second is part of the summary sheet (horizontal) and 3rd is the Summary sheet Transposed.  Any help is appreciated! 

Kerry_83_0-1646884716026.png

Kerry_83_1-1646884806696.png

Kerry_83_2-1646884995248.png

 

 

 

@Kerry_83 I don't have the sheet to test on but I think a better approach would be:

=SUMIFS(Transpose!$S$1:$S$1000, Transpose!$A$1:$A$1000,"<="&EOMONTH(A4,0), Transpose!$A$1:$A$1000,">="&EOMONTH(A4,-6), Transpose!$B$1:$B$1000,"Interest")

That said I have a couple of notes:

this assumes column A dates are actually dates and not TEXT that just looks like a date

that you actually do want last 6 months of values (including all of the month for the given date as I did here or should it only be <= that exact date).  Part of this question is because I noticed a couple of the months had an ADDED entry.  is that why the previous included 7 entries instead of 6?  Were you forced into 6month in order to guarantee a consistent number?  With this formula you can choose the time period.

To change from Interest to Cash Int or Accrued Int just change the "Interest" accordingly.