# Complicated Sumproduct Array functions problem

#### Foeth

##### New Member
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?

And NOT

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.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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...

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

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.

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.

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

Replies
5
Views
1K
Replies
4
Views
2K
Replies
8
Views
886
Replies
12
Views
2K
Replies
0
Views
2K

1,221,497
Messages
6,160,154
Members
451,625
Latest member
sukhman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back