sumproduct using offset formula by reversing column

[kindi1000]

I was trying to calculate the sumproduct of two arrays of data. The catch was I needed to reverse the order of one of the arrays and then multiply.

The formula I used was:

{=(OFFSET($E11,0,COLUMN()-COLUMN($E11:J11)))*($E62:J62)}

when it calculates, it returns just the product of the first item in each array.

I have not really used offset before and I do not understand why it does not calculate the sumproduct of both arrays. The data looks like:

E F G H I J
11: 1% 12% 23% 17% 14% 14%

62: 62,500 - 70,000 267,500 294,000 -


Please advise! Thanks!

 

[Aladin Akyurek]

I was trying to calculate the sumproduct of two arrays of data. The catch was I needed to reverse the order of one of the arrays and then multiply.

The formula I used was:

{=(OFFSET($E11,0,COLUMN()-COLUMN($E11:J11)))*($E62:J62)}

when it calculates, it returns just the product of the first item in each array.

I have not really used offset before and I do not understand why it does not calculate the sumproduct of both arrays. The data looks like:

E F G H I J
11: 1% 12% 23% 17% 14% 14%

62: 62,500 - 70,000 267,500 294,000 -


Please advise! Thanks!

=SUMPRODUCT(N(OFFSET(E11,0,COLUMN(E11)+COLUMNS(E11:J11)-COLUMN(E11:J11)-1,1)),E62:J62)

Just needs enter. BTW, I assumed that you want to reverse E11:J11.

 

[kindi1000]

thanks

thanks...i couldn't figure it out and i spent hours trying to get it right...

 

[ilonaaa]

=SUMPRODUCT(N(OFFSET(E11,0,COLUMN(E11)+COLUMNS(E11:J11)-COLUMN(E11:J11)-1,1)),E62:J62)

Just needs enter. BTW, I assumed that you want to reverse E11:J11.

Works exactly as intended!