Complicated Sumproduct Array functions problem

[Foeth]

Is it possible to have a Sumproduct array function when you enter {=Sumproduct(A^B)} with A the data to be evaluated per cell, but with B as a random length array, not to be evaluted per entry but completely per individual array function result?

Example: {answer}={=sumproduct(A^B)}

Answer1=Sumproduct(A1^B)
Answer2=Sumproduct(A2^B)

And NOT

Answer1=Sumproduct(A1^B1)
Answer2=Sumproduct(A2^B2)


In other words, can you fool array functions? Note that the length of A never coincides with the length of B, except in this example.

 

[PaddyD]

Clear as mud here, but if I get you right, the answer is 'No' - the arrays in the sumproduct need to be the same size.

How about posting back with a detailed description of the problem you are actually trying to solve, the data you've got & the expected outcomes - we might be able to come up with an alternative solution...


Paddy

 

[Foeth]

I have a simple series formula looking like:

y=<FONT FACE="Symbol">S<FONT FACE="Arial"> (A * c1^B * c2^C * c3^D * c4^E)

So, I have four input variables c1:c4 all being raised to a power from the vectors B,C,D,E. These vectors have roughly 70 entries, all integers from 0 to 6.

So, this looks like:

y=A(1) * c1^B(1) * c2^C(1) * c3^D(1) * c4^E(1)
+A(2) * c1^B(2) * c2^C(2) * c3^D(2) * c4^E(2)
+....
+A * c1^B * c2^C * c3^D * c4^E

SUMPRODUCT does exactly that
y= SUMPRODUCT (A;c1^B;c2^C;c3^D;c4^E)

The problem, well problem, is that these functions need to be evaluated in 10 cells, giving 10 different c1:c4's, but the same A,B,C,D,E. Secondly, these formulae are used iteratively in the sheet, without any VBA code. Array functions are much faster that normal formula and evaluating these expressions at speed is important. A nice extra is that these formula are expressed in, if possible, 1 cell. The iterative routine is used to find a solution, Goal seek style. Goal seek is far too slow to be used, so a nice "circle reference" is used with a "smart" routine.

The SUMPRODUCT function is ideally suited for these operations, but because I use two different array lengths, one remaining to be evaluated per entry and one changing through the array, the SUMPRODUCT array function doesn't work. I have the feeling array functions and excel functions as SUMPRODUCT cannot function together by their very nature.

The sheet suffers from numerous invalid page faults and I'm trying to simplify it, or restricting memory use. The fault is an exception in Excel.exe (or dll) at module 167:etc, consistently with all users.
This message was edited by Foeth on 2002-09-25 23:45

 

[PaddyD]

That's a bit clearer, but I'm still having difficulties visualising the way the data is set up. Could you consider downloading the html maker addin from the link below this post & posting back with a representative snapshot of your data. It would also help to have a fully worked example, with start data, parameters, expected results etc.

Paddy

 

[Foeth]

(I'm postin gin several entries, I lost half of my inital post)

Here's the base polynomial data:

Series.xlt
	A	B	C	D	E	F	G
7	P/D	J	BAR	z	KTT	KTN	KQ
8	0	0	0	0	0.00880	0.00000	0.00379
9	0	0	0	1	0.01440	0.00000	0.00000
10	0	0	0	2	-0.00061	0.00000	0.00000
11	0	0	1	1	-0.01259	0.00000	0.00000
12	0	0	1	2	0.00069	0.00000	0.00000
13	0	0	2	0	-0.05072	0.00000	0.01590
14	0	0	2	2	0.00000	0.00000	-0.00018
15	0	1	0	0	-0.20455	0.00000	0.00000
16	0	1	0	1	0.00000	0.00000	-0.00371
Transfer

This message was edited by Foeth on 2002-09-26 23:51

 

[Foeth]

So, there;s three sets of coefficients, KTT, KTN and KQ, called Ser_KTT etc. They form three different formula and can be called independently. I'll focus on one, they're all teh same except for the actual values.

There's 4 rows of coefficients with power values. They are shared by the three formula for the 3 coefficients KTT, KTN, KQ.

So, manually, the KTT formula needs to be calculated as follows:

KTT=P/D^0 * J^0 * BAR^0 * z^0 * 0.00880
+P/D^0 * J^0 * BAR^0 * z^1 * 0.01440
+P/D^0 * J^0 * BAR^0 * z^2 * -0.00061
+...
With the sheet in the post above you see how the data is being used. P/D, J, BAR and z are values entered in evaulation sheets. This sheet is merely containing the right ingredients for the formula to be used later on. The total number of entries is roughly 70, but depending on the availabitly of more extensive polynomials, it may well be longer.

 

[Foeth]

Now a typical evaluation:

Series.xlt
	J	K	L	M	N	O
15	BAR	P/D	J	KTN	KTT	10KQ
16	-	-	-	-	-	-
17	0.600	1.081	0.000	0.0000	0.4690	0.7375
18	0.600	1.025	0.043	0.0000	0.4350	0.6519
19	0.600	0.974	0.083	0.0000	0.4023	0.5779
20	0.600	0.930	0.122	0.0000	0.3710	0.5137
21	0.600	0.890	0.159	0.0000	0.3413	0.4580
22	0.600	0.854	0.194	0.0000	0.3132	0.4093
23	0.600	0.823	0.228	0.0000	0.2868	0.3667
24	0.600	0.795	0.261	0.0000	0.2620	0.3293
25	0.600	0.770	0.292	0.0000	0.2387	0.2964
26	0.600	0.748	0.321	0.0000	0.2169	0.2674
Selection

 

[Foeth]

There are 10 rows (fixed). You can thus see 10 data sets (BAR, P/D, J, z), z being the only constant throughout the entire sheet.

The three formula are next to it. They're more or less the same. In the Sumproduct code, only near the right bracket a different Ser_KTT or Ser_KTQ is called (Plus an IF statement for the last two which can be ignored):

KTN=SUMPRODUCT(K17^Ser_PD;(M17+1E-20)^Ser_J;J17^Ser_Bar;Set_z^Ser_z;Ser_KTN)
KTT=SUMPRODUCT(K17^Ser_PD;(M17+1E-20)^Ser_J;J17^Ser_Bar;Set_z^Ser_z;Ser_KTT)
KQ=SUMPRODUCT(K17^Ser_PD;(M17+1E-20)^Ser_J;J17^Ser_Bar;Set_z^Ser_z;Ser_KTQ)

So I have an array of 10 data sets for a set of formula using 70 coefficient entries.

Sumproduct in an array will try to evaluate both arrays entry by entry, which it cannot due to different array length, but even if it did work, that's not what it should do! What is should do in the array formula is use 1 data set and the entire coefficient array of 70 numbers.

The reason (which I left out until now) is that using these 30 formula each using 5*70 entries roughly 10 times in an iterative loop can be a bit slow.

Array function speed up considerably, but fail on Sumproduct functions or other functions that contain ranges per entry.

This brings me back to the initial question: can you manage arrays to behave nicely? I believe the answer is already given as "no", as I've found out as well. I'll still continue my holy quest of speeding this sheet up.

BTW: Wow! What a plugin!
This message was edited by Foeth on 2002-09-27 00:16