Complex Vector Computations

[dmillier]

I'm not completely comfortable with the OFFSET function or the PRODUCTSUM function and I have been trying to figure out (with no luck) how to do the calculations in the attached spreadsheet. I have to do the calculations for a total of 360 rows down and therefore 360 columns across, so I thought it would be more straightforward to use the aforementioned functions.I tried to make it as straight forward as possible. Let me know if you have any questions. Thanks.

[shg]

Plerase take a few minutes to read the forum rules, and then amend your thread title accordingly.

Also, I don't have a clue what you're trying to do from your example. Someone else may, or perhaps you could explain some more.

[DonkeyOte]

EDIT: oops - sorry shg - didn't notice the title request

I think this will be difficult to achieve using a SUMPRODUCT (non-array entry) given the nature of the A3 calculation

You could perhaps use the following Array:

G10:
=SUM(($D$10:$D10*LOOKUP($B10-$B$10:$B10,{0,5,8},$A$1:$A$3))*IF($B10-$B$10:$B10>=8,INDEX($C$10:$C10,1+ROW($C10)-ROW($C$10))*300,1))
confirmed with CTRL + SHIFT + ENTER
copied down

The above replicates your expected results though does make assumption of whole numbers in B10:B18

[dmillier]

Thanks again Donkeyote. Your name truly applies to amazing Excel technical abilities. At first I couldn't get it to work, then noticed/realized it was a vector calculation and saw your note. After using Ctrl + Shift + Enter it worked perfectly. Thanks again.