How to reverse the direction of a range in formulas?

[Rnkhch]

Hello,

I have noticed that every time I have a range in formulas, say A2:D2, Excel takes the range elements from A to D. And if I try to write D2:A2, upon pressing cntrl+shift+enter, Excel auto-corrects it to A2:D2. So I have been trying to look for a way to specify a range such that Excel would consider it in the reverse orientation. Here is a simple example for the formula {=SUM(A1:D1*A2:D2)} which returns the value of 40 assuming the default direction of A2:D2 range:

However, the result would be 30 if the direction of A2:D2 was reversed to D2:A2:

But could this have been achieved without having to manually rewrite the range in the reverse order? (I'm trying to learn a generic way of doing this so that I can apply it to various situations as I often come across long ranges that have been written in the reverse order and flipping them manually would be cumbersome).

Thanks for any input!!!

 

[Peter_SSs]

could this have been achieved without having to manually rewrite the range in the reverse order?

Yes it could, but is is not simple and it uses the volatile function OFFSET. See F2 below.

If it was me I would be looking to use a spare range to reverse the order as I have done with A4 copied across and then you can use the simple SUMPRODUCT in F4.

Book1
	A	B	C	D	E	F
1	1	2	3	4
2	2	3	4	5		30
3
4	5	4	3	2		30
Reverse Range

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(A1:D1,N(OFFSET(D2,0,COLUMN(A2)-COLUMN(A2:D2))))
A4:D4	A4	=INDEX($A2:$D2,COLUMNS($A2:$D2)-COLUMNS($A4:A4)+1)
F4	F4	=SUMPRODUCT(A1:D1,A4:D4)

 

[Rnkhch]

Awesome! Thanks much!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[jasonb75]

It can also be done in one cell without using OFFSET,

Book1
	A	B	C	D	E	F
1	1	2	3	4
2	2	3	4	5		30
Sheet7

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(A1:D1*INDEX(A2:D2,N(IF({1},INDEX(COLUMN(D2)-COLUMN(A2:D2)+1,)))))

The second INDEX function is not strictly necessary, I've only used it to omit the need for array confirmation.

 

[Rnkhch]

Fantastic! Thanks a lot!!! Also how would it look like without the second INDEX?

 

[Bo_Ry]

Another one F2
=SUMPRODUCT(A1:D1,MOD(LARGE(INDEX(COLUMN(A2:D2)*100+A2:D2,),COLUMN(A2:D2)),100))

 

[jasonb75]

Fantastic! Thanks a lot!!! Also how would it look like without the second INDEX?

Like this

=SUMPRODUCT(A1:D1*INDEX(A2:D2,N(IF({1},COLUMN(D2)-COLUMN(A2:D2)+1))))

 

[Rnkhch]

Thanks jasonb75 and Bo_Ry!!!!