Forum Discussion

Kerry_83's avatar
Kerry_83
Copper Contributor
Mar 09, 2022

Changing an array formula for a different cell range

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...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Mar 09, 2022
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.
  • Kerry_83's avatar
    Kerry_83
    Copper Contributor
    Mar 10, 2022

    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! 

     

     

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Mar 10, 2022

      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.

Resources