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.

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

I chanced upon this question because I was trying to find an answer to this very question, in particular a solution to be used with SUMPRODUCT.

Previous post with link was useful, and the following has been devised. Please, note that for simplicity and clarity original references have been changed to make spreadsheet formulas easier to understand. Necessary changes can be achieved by using INDIRECT and R1C1 reference style if reversal needs to be applied to columns (INDIRECT, COLUMN and COLUMNS function documentation would prove useful).

In Excel (checked in Excel 2010), the formula that SUMPRODUCTs an array in A6:A8 with the reversal of the array in B6:B8 (let's say B8:B6) could be

=SUMPRODUCT(A6:A8,N(OFFSET(B6:B8,ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0)))

The reversing part for Excel is N(OFFSET(B6:B8,ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0)), it can also be used as a multi-cell array formula (ctrl-shift-enter) anywhere - the array to be reversed is in B6:B8.

Brief explanation: N() is necessary to convert the references returned by OFFSET into an array of values that can be used inside SUMPRODUCT. (OFFSET without N() render the formula inoperative within SUMPRODUCT, and it converts most non numbers into 0, as expected it converts TRUE to 1.)

OFFSET takes the array on the spreadsheet and puts the values in reverse order. (In this case, offset original at 3-1=2 goes new array position 0, 3-2=1 goes to 1, and finally 3-3=0 goes to 2.) ROW() function creates an array of consecutive numbers that can be subtracted from the length of the total array [please note, no final S, and A1:A3 has been used to obtain {1,2,...}, up to the number of rows in the original array/range.]

This formula does NOT work in OpenOffice/LibreOffice spreadsheet application. But INDEX can with an analogue approach becoming even more flexible.

OpenOffice/LibreOffice can use the same approach but with the INDEX function. OpenOffice/LibreOffice solution does not work in Excel as Excel does not accept arrays in INDEX's row_num/col_num (any array as argument there becomes the single top element of the array).

In OpenOffice/LibreOffice (checked in Apache OpenOffice 4), the formula that SUMPRODUCTs an array in A6:A8 with the reversal of the array in B6:B8 could be

=SUMPRODUCT(A6:A8,INDEX(B6:B8,1+ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0))

The reversing part for OpenOffice is INDEX(B6:B8,1+ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0), it can also be used as a multi-cell array formula (ctrl-shift-enter) anywhere - the array to be reversed is in B6:B8.

Excel version with this approach (thanks, XOR LX: see here) which might be very useful as INDEX can take arrays whereas OFFSET can only take references to a worksheet:

=SUMPRODUCT(A6:A8,INDEX(B6:B8,N(INDEX(1+ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),,)),0))

[As it happens with Excel's OFFSET version above, the formula with the N(INDEX([...],,)) modification does not work in OpenOffice, the wrapping function must be taken out if using that application.]

The approach is analogue to the one used in Excel for OFFSET. Take into account that INDEX uses indexes starting with 1 whereas OFFSET starts with 0. As it happens with the previous case, an array is dynamically created from the row numbers in A1:A3 to be used as both template for the array and position change index.

This very late answer is unlikely to help the original poster, but it might save time to future users with a similar question.

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.)