conditional summing of arrays

[John Vieren]

I would like to sumproduct 2 arrays up to the row where it first exceeds
a given number. For example assume 2 arrays are as follows : 1,2,3,4,5 &
2,3,4,5,6 the sumproduct is 2,8,20,40,70. So if my given number was say
30, the answer would be 4. if the number was say 80, the result would be
NA. Can anyone help me with this problem? Thank you and kind regards JV

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Peo Sjoblom]

This can probably be done easier

=INDEX(LARGE(ROW(INDIRECT("1:"&ROWS(Array1))),ROW(INDIRECT("1:"&ROWS(Array1)
))),MATCH(G1,LARGE(Array1*Array2,ROW(INDIRECT("1:"&ROWS(Array1)))),-1))

entered with ctrl + shift & enter

First array named Array1 and so on, the value you want to check is in G1

Btw, your data when after sumproduct look like {2;6;12;20;30}

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom