Reverse Order of Two Dimensional Arrays Using Offset Function

[Etoilebrilliant]

First off, thank to everyone on this board. In fact the question I'm posing is a variation to a question that was successfully asked here and from which I learnt a lot.

Background: I've been using the following formula for aggregating the product of two arrays where the latter array needs to be presented in reverse order - needless to say it works perfectly

=SUMPRODUCT(OFFSET($C12,0,0,1,C$2),N(OFFSET(Vectors!C7,0,2-COLUMN(Vectors!$B7:B7),1,1)))

Now I want to take the formula to the next stage and use it to aggregate the product of multiple arrays

=SUMPRODUCT(A1,A2,A3:Va,Vb,Vc)

Or rewritten as:

=SUM(V1xVa, V2xVb, V3xVc)

The SUMPRODUCT function is excellent and can deal with multiple arrays natively. The problem arises when I wish to reverse the order of the second set of arrays.
The existing formula (see below) only seems to work with singular arrays

N(OFFSET(Vectors!C7,0,2-COLUMN(Vectors!$B7:B7),1,1)))

Any suggestions. At the moment, I'm inclined to write a user defined function but this seems clumsy. Bear in mind that the number of items of each array will change as per column - so creating a helper field with won't be a solution.

Thanks in Advance

 

[jasonb75]

There is too little useful information in a screen capture, among other things your formula refers to C$2 which appears to contain a text string, which subsequently is not valid to use as the offset range width.

For the sample to be useful it would need to be posted using XL2BB and include all of the data that the formula refers to, which included that on the vectors sheet.

See if this does what you want, will need to be array confirmed with Ctrl Shift Enter if not using office 365 (please update your Account Details to show which version you are using, remember to scroll down and save changed after selecting your version).

=SUMPRODUCT(OFFSET($C5,0,0,3,C$2),N(IF({1},OFFSET(Vectors!C10,0,2-COLUMN(Vectors!$B7:B7),3,1))))

 

[Etoilebrilliant]

Apologies, you are correct to pull me to one side. Here I have uploaded the revised file in XL2BB.

Many thanks once again.

Volvo Care Depreciation Model Luke Sandpit.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Cash Flows (Portfolio Effect)	1-Jan-22	1-Feb-22	1-Mar-22	1-Apr-22	1-May-22	1-Jun-22	1-Jul-22	1-Aug-22	1-Sep-22	1-Oct-22	1-Nov-22	1-Dec-22	1-Jan-23
2		Month 0	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
3	Vectors
4	Asset Depreciation Curve
5	Vehicle Depreciation A	0.00%	1.41%	1.21%	1.09%	1.62%	1.58%	1.03%	1.10%	1.12%	1.74%	1.64%	1.42%	1.82%
6	Vehicle Depreciation B	0.00%	1.11%	1.23%	1.62%	1.60%	1.94%	1.77%	1.62%	1.73%	1.55%	1.78%	1.15%	1.88%
7	Vehicle Depreciation C	0.00%	1.34%	1.81%	1.69%	1.76%	1.49%	1.95%	1.46%	1.39%	1.02%	1.79%	1.52%	1.64%
8
9	Asset Volumesby Cohort
10	Vehicle Volumes A	0.00	621.38	488.65	349.40	987.33	350.29	139.64	720.70	92.87	218.51	813.21	533.74	590.84
11	Vehicle Volumes B	0.00	267.54	586.22	862.35	339.24	894.78	292.02	406.66	318.97	173.69	773.44	153.56	471.26
12	Vehicle Volumes C	0.00	494.09	166.80	719.19	818.33	878.92	664.60	727.71	658.24	20.40	208.61	615.02	168.99
13
14	Aggregate Depreciation for All Cohorts
15	Vehicle Volumes A	0.00	8.74	14.37	17.56	33.51	38.36	36.73	49.08	48.69	50.14	66.11	71.82	82.67
16	Vehicle Volumes B	0.00	2.96	9.79	21.11	28.18	42.65	49.70	59.48	63.58	68.73	78.12	79.45	88.40
17	Vehicle Volumes C	0.00	6.62	11.19	21.03	35.50	49.07	63.45	72.23	84.23	83.11	86.66	89.80	95.11
18	This works . But I need to produce the formula 'row by row'. I wish to aggregate
19		Formula for Rows 15 to 17 -----------------> =SUMPRODUCT(OFFSET($C10,0,0,1,C$2),N(OFFSET(C5,0,2-COLUMN($B5:B5),1,1)))
20
21	Multiple Arrays (2nd Array not Reversed)	0.00	18.33	34.47	64.42	100.26	136.22	155.82	180.98	196.69	203.38
22	This works - however, the depreciation curve is not reversed
23		Formula for Row 21 -----------------> =SUMPRODUCT(OFFSET($C5:C7,0,0,3,C$2),OFFSET($C10:C12,0,0,3,C$2))
24
25	Multiple Arrays (2nd Array Reversed)	0.00	#VALUE!
26	This is where I need help - failed attempt
27		Formula for Row 25 -----------------> SUMPRODUCT(OFFSET($C5:C7,0,0,3,C$2),N(OFFSET(Vectors!C10,0,2-COLUMN(Vectors!$B7:B7),3,1)))
Vectors

Cell Formulas
Range	Formula
C1:N1	C1	=EOMONTH(B1,0)+1
C2:N2	C2	=B2+1
C15:N17	C15	=SUMPRODUCT(OFFSET($C10,0,0,1,C$2), N(OFFSET(C5,0,2-COLUMN($B5:B5),1,1)))
C21:K21	C21	=SUMPRODUCT(OFFSET($C5:C7,0,0,3,C$2),OFFSET($C10:C12,0,0,3,C$2))
C25	C25	=SUMPRODUCT(OFFSET($C5:C7,0,0,3,C$2),N(OFFSET(Vectors!C10,0,2-COLUMN(Vectors!$B7:B7),3,1)))

Named Ranges
Name	Refers To	Cells
ArrayDepreciationCurveActual	=Vectors!$B$6:$DR$6	C16:N16

 

[jasonb75]

I can see the problem now. If it was something that was a more common occurrence then I may well have noticed it without having to test it first, although I'm sure it has happened more often this is only the second time that I can recall seeing it.

If you try the formula that I suggested in post 2 you will see that it gives a result in the first column as being only a single column there is nothing to reverse at that point. As soon as the formula is expanded to the second column it creates an error. Although I have not seen anything definitive explaining the causes, I believe that it is the result of the arrays being produced containing more dimensions than the functions can cope with. Effectively you have Arrays x Rows x Columns. For the formula to return a valid result at least one of those must be singular, as soon as the smallest dimension contains more than 1 element it fails.

There may be as yet undiscovered ways to work around it with other functions but given how unlikely I think it will be that an accurate working formula can be found, I would suggest going with the alternative option of using a UDF for the task.

 

[Etoilebrilliant]

Thanks for that - back to the VBA for a UDF.