reversing rows in excel formulas

tags:

excel

views:

answers:

reversing rows in excel formulas

(A) SumProduct( A1:A3,B1:B3) == A1*B1 + A2*B2 + A3*B3

Instead, what I'm after is

(B) SumProduct( A1:A3, Reverse(B1:B3)) == A1*B3 + A2*B2 + A3*B1

Is there a clean way to achieve this in excel 2003 / excel 2007 ? The natural ordering of these values is A1->A3 and B1->B3, so reversing the meanings of the cells is unsatisfactory; but creating a reversed copy of B1:B3 elsewhere in the worksheet seems clumsy.

+2 A:

Check the topic "Transposing A List Of Data" in http://www.cpearson.com/EXCEL/lists.htm

e.tadeu 2010-02-04 18:50:17

That solution creates a reversed copy elsewhere in the spreadsheet.

VoiceOfUnreason 2010-02-04 19:09:32

Just hide the column with the reversed copy anyway... :)

e.tadeu 2010-02-09 14:15:31

The relevant section is called "Reversing The Order Of A Row". It's a good find. It solved it how I was about to suggest, but better.

Oddthinking 2010-02-21 01:30:50

I cannot see a solution that doesn't involve (a) custom functions in VBA (or similar) or (b) an extra column with partial results.

If you don't like column C becoming a (hidden) reverse list, would you accept column C becoming a list like: A1*B3, A2*B2, A3*B1, which could then be summed? It would be possible to use a similar formula to the one mentioned in @e.tadeu's answer to obtain this (using OFFET and ROW functions.)

Oddthinking 2010-02-21 01:34:10

I'm accepting this, as the first paragraph is a variant spelling of "no, it can't be done."

VoiceOfUnreason 2010-05-06 19:30:23

ansaurus

tags:

views:

answers:

reversing rows in excel formulas

related questions