sumproduct help

[Guest]

Im learning the uses of this as i go along, and still trying to figure this
out by not using ctrl+shift+ enter.....

Is there a way to use sumproduct (or something else) to return a list... ie
that last array without summing it?

 

[Frank Kabel]

Hi
whyt do you mean with 'last array'?

 

[Domenic]

Not sure if this is what you mean...

With the following table in A1:C5:

1.....6.....11
2.....7.....12
3.....8.....13
4.....9.....14
5.....10....15

A10, copied down:

=OFFSET(A1,,MATCH(9.99999999999999E+307,1:1)-1)

In this case, the formula would return the last value in each row...

11
12
13
14
15

If in this case C1 and C3 contain zero's and you want the following
returned...

0
12
0
14
15

....instead of...

6
12
8
14
15

....an array formula could be provided as one method of achieving this.
Post back if that's the case.

Hope this helps!