Dynamic Range???

[kmham]

I use both Excel 2013 & 2016

A1 has the header "Date"
Cells A2-A25 has the values 1/1/2016, 2/1/2016 and so on formatted/displayed as Jan-16, Feb-16, etc

B1 has the header "Amount"
Cells B2-B25 has values $1, $2, ... $24

Cell E1 has a variable value: it currently has 9/1/2017 (displayed as Sep-17), but can change
Cell F1 has a variable value: currently has 12, but can change

The result for the above scenario for Cell C22 (next to Sep-17 $21) will be $186 which is the sum of B11:B22.

If E1 was changed to 8/1/2017, then cell C21 will be $174 which is the sum of B10:B21. Keeping 8/1/2017 and changing cell F1 to 10, the sum in cell C21 would now be $155 which is the sum of B12:B21.

Both variable values in E1 and F1 are driven by a bunch of other stuff. Too many details to get into here, but what I'm trying to accomplish is the same.

Essentially the value in E1 drives my END point and F1 drives how many rows in column B need summed ending on the Date Selected in cell E1.

Dates will be input in Ascending order with newest dates input on the next available row (currently row 26 would be the next row that the user would input 1/1/2018).

I will have a SUM formula in Column C for all 24 rows of data.

I'm not 100% sure on this next piece, but I think every result in Column C can be $0 where the DATE is NOT the same date as cell E1. Meaning I only need the total for Row 22 where cell A22 says Sep-17 because E1 is also Sep-17. But my formula in Column C needs to be the same all the way because if E1 is changed to Aug-17 then I need the info to update automatically. Sorry if this isn't 100% clear.

ANOTHER TRICKY ITEM: Say 8/1/2016 was chosen and 12 was the value in F1. 8/1/2016 is on row # 9 therefore there are only 8 rows available to sum (1/1/2016-8/1/2016), therefore the sum needs to take the MIN of 8 or 12, 8 in this case, so the sum range would be B2:B9 and total $36. Hope that makes sense. If the "8" needs calculated and stored in another cell, that is okay if that's what is needed.

I'd prefer not to use OFFSET as I've read that formula is more volatile. Does INDEX work better?

I hope this is possible, I'm just really struggling to figure out how to make this work. Many thanks for the assistance!

 

[Marcelo Branco]

Maybe

Formula in C2 copied down
=IF(A2=E$1,SUM(B2:INDEX(B$2:B$1000,MAX(1,MATCH(E$1,A$2:A$1000,0)-F$1+1))),"")

Hope this helps

M.

 

[kmham]

Thank you Marcelo! Worked perfectly in my example.

Unfortunately my file is more complex (too much to explain here) and it's working but not summing just right. My data/headers (in table format) starts on row 11 with data in rows 12-37 and will grow each month. It currently ends on 11/1/2017. When I input 12/1/2017 that'll start on row 38. Column A has my Month/Year value, Column B has the Amount, and Column J is where I'm putting this new formula (columns C-I has various other data that is irrelevant to this particular need).

Anyway, Sep-2017 is on row 35 (row 12 starts with Month/Year 10/1/2015). The 12 rows that I'm summing represent the months/years Oct-16 thru Sep-17 and are totaling 20.8 in column B, but the formula is giving me 22.8 in cell J35, but I'm expecting 20.8 (values in order for Oct-16 thru Sep-17 are 1.85, 1.83, 1.71, 1.5, 1.76, 2.76, 1.52, 2.15, 1.9, 0.97, 1.99, 0.86)


I'm assuming that maybe I have an issue because of the extra rows 1-9 at the top (which has various info but not table data) and then my header/table starts on row 10. Is that causing this formula to not "index" correctly? I'm not really sure what or how Index works either.

 

[kmham]

Sorry, never mind. I finally figured it out. For whatever reason the first part of the SUM part of the formula input $B$12 when it needed to be [@Actual] which "Actual" is the header name of Column B for the table data set. It works now!!! Thank you again.

 

[Marcelo Branco]

Sorry, never mind. I finally figured it out. For whatever reason the first part of the SUM part of the formula input $B$12 when it needed to be [@Actual] which "Actual" is the header name of Column B for the table data set. It works now!!! Thank you again.

You are welcome. Glad to help

M.

 

[kmham]

Marcelo...I had a slight change in my project. Boss now needs something a little different but pretty close to what you already provided. I just need to remove the IF part of the statement that matches the Date as well as the part that uses the Max & Match for the date (i.e. cell E1).


So I took the following formula (in cell C2):
=IF(A2=$E$1,AVERAGE(B2:INDEX($B$2:$B$25,MAX(1,MATCH($E$1,$A$2:$A$25,0)-$F$1+1))),"")

And modified it as follows (in cell D2):
=AVERAGE(C2:INDEX($B$2:$B$25,MAX(1,-$F$1+1)),"")

But I only get #VALUE !. I know I need to get rid of the E1 reference since I no longer need it to match on the Date. I want every row (for every Date value) to now show me the Running Average, of the last 12 months (because 12 is in cell F1, but this can change). And I still need the formula to provide the running average as the max # of months if only say 5 months are available. Hope this makes sense.

 

[Marcelo Branco]

Maybe...

D2 copied down
=AVERAGE(B2:INDEX(B$2:B$25,MAX(1,ROW(B2)-F$1)))

M.

 

[kmham]

Took me a bit (way longer than it should have, but hey I'm not an expect. Thank you again to Marcelo as your formula got me heading in the right directly (again!).

I'm using the following formula to accomplish the following: Calculate the Running Average of Column B - calculated in Column C. The user selects the number of months they want reported. I currently have 26 months of data starting 10/1/2015 and ending 11/1/2017. Column A is Date, Column B is Amount, and Column C is the formula for running average. Column headers are in row 1.

=AVERAGE(OFFSET(B27,MAX(-COUNTA($B$2:B27),$E$1+1),0):B27)

Cell E1 is currently populated as 12, but this number can change as the user selects how many months of data they wanted reported.