Offset, Arrays, and more Offset

[lucholland]

Hello Excel experts!

I'm a long time reader, first time poster. Please be gentle I'm running this in Windows on Excel 2007.

I'm looking to see if this is possible using Excel formulas, rather than a VBA solution. If this is too complicated for formulas, let me know.

I have some annual data, which does not start in A1. The data is created by some previous spreadsheet formulas on the worksheet, so I can't relocate it.

Year values in row 85 go back to 2000, counts in row 86 go back that far as well. Future years have blank entries. Here is an example of what it looks like:

	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
85	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
86	30	18	12	35	45	8	17

I also have some control cells, which are the core of the table generation:
M80 (target year): 2010
M81 (spread): 2
M82 (window): 4

The purpose is to create a DYNAMIC TABLE that is "window" years wide starting with the target year. The header row would be calendar years. This header row for this example would be

2010

2011

2012

2013

These could start anywhere. For this example, destination for the start of this range would be Q90 (i.e. Q90 :=2010).

The values to be placed in the dynamic table (starting at Q91) would be:
The cumulative sum from the start of the values up to the "target year +1", divided by the "spread". i.e. =SUM($G$86:Q86)/M81.
This same value repeated a second time (since spread is 2). i.e. =SUM($G$86:Q86)/M81.
The remaining years (Window - spread) contain the values under the year.

The end result I would want is

2010	2011	2012	2013
=SUM($G$86:Q86)/M81	=SUM($G$86:Q86)/M81	=R86	=S86
47.5	47.5	45	8

The dynamic table changes (i.e. grows or shrinks) based on the parameters (start year, window, spread).

I managed to manually insert an offset array function for the "non cumulative" years, but I get #N/A errors since my destination array is larger (except for the largest window sizes) than my results. I did this via the select range, press F2, enter formula, CTRL+SHIFT+ENTER.

Like I said at the start - this is complex and I could write some VBA code to handle it, but I'd like to try a formulaic approach first.

Thanks for listening! I look forward to your responses

[ChemistB]

Okay, question 1.
Why does it start in Q90? Because 2010 is in P? (i.e. starts 1 to right of target year)? or just random choice?

[lucholland]

Okay, question 1.
Why does it start in Q90? Because 2010 is in P? (i.e. starts 1 to right of target year)? or just random choice?

Hey ChemistB,

Semi random choice. Not significant/important except it ensures if the table grows to the right nothing gets overwritten.

[ChemistB]

Okay, here's what I came up with. I gave named ranges to your target, spread and window. I also defined a "datatable" named range as follows

=Sheet1!$F$85:INDEX(Sheet1!$86:$86,COUNT(Sheet1!$86:$86)+COLUMN(Sheet1!$E86))

because your years (at least in my sheet) begin in column F.

In Q90
= Target
In R90 copied to the right
=IF(COLUMN()-COLUMN($Q$90)>=Window,"",Q90+1)

In Q91 copied to the right

=IF(COUNT(P$90:$Q90)=Window,"", IF(COUNT($Q$90:Q90)>Spread,HLOOKUP(Q90,datatable,2,FALSE), SUM($F$86:INDEX(86:86,MATCH($Q$90,85:85,0)+1))/Spread))

See attachment.
Does that work for you?

[lucholland]

I'm amazed!!

From my quick test I ran on the attached file, it appears to work exactly as needed. Later today, I'll go through additional test scenarios to confirm.

I'm impressed with the elegance of the solution, too. Very nice work!

[ChemistB]

Thank you. Hope it all works out.

[lucholland]

Thank you. Hope it all works out.

Follow-up: ran through my 10 test cases, and they all worked perfectly.

Thank you for the solution. I was so focused on using OFFSET, I ignored other solutions.