excel - Reverse one of the array's order in an SUMPRODUCT

Question

I am in the process of trying to calculate a couple of values The first value I am trying to calculate is this math formula:

RL = (P₅*a₁ + P₄*a₂ + P₃*a₃ + P₂*a₄ + P₁*a₅)/L

The Second formula has RL in it but suffers from the same problem.

M = RL * x - P₅(x-a₁) - P₄(x-a₂) - P₃(x-a₃) - P₂(x-a₄) - P₁*(x-a₅)

When I look at these I see a SUMPRODUCT.

My P₁...P₅ values are layed out in the same order in the range $C$12:$G$12 Or named range Axle_P.

My a₁...a₅ values are layed out in the same order in the array offset($X$12,$AL$13,0,1,5) where X12 is above the first row of a values and in the first column of a values. AL13 holds the row number for the row of interest.

L is cell $D$8
x is cell $M41

So I want to use the formula

For calculating RL in say cell AA1

=SUMPRODUCT(Axle_P,offset($X$12,$AL$13,0,1,5))/$D$8
OR
=SUMPRODUCT($C$12:$G$12,offset($X$12,$AL$13,0,1,5))/$D$8

For calculating M

=$AA$1*$M41 - SUMPRODUCT($M41-offset($X$12,$AL$13,0,1,5),Axle_P)
OR
=$AA$1*$M41 - SUMPRODUCT($M41-offset($X$12,$AL$13,0,1,5),$C$12:$G$12)

The problem is this results in the wrong sequence of:

(P₁*a₁ + P₂*a₂ + P₃*a₃ + P₄*a₄ + P₅*a₅)

How can I revers the order of either the P array or the A array?

Now I do plan on combining these eventually into one cell. when I am done, I plan on copying the formula down through 5 rows. So essentially 5 different x values with output in 5 cells.

VBA not a valid option and I would prefer to avoid the use of CSE but if it is the only way then I will suffer.

now if you are wondering why I dont change M to

M = RL * x - (RL *L)

is that there are some other caveats I will be applying after I get this figured out that will set P_n-i+1 to 0 if a_i is less than 0 or greater than L and I have that part planned out. At least I do if this can be done in a SUMPRODCUT.

Answer 1

With help of OFFSET and an array of column offsets it is possible to reverse a range.

Example:

The trick is to get each single cell from the range beginning with the end and with offset {0,-1,-2,-3,-4}. The N function is neccessary because OFFSET used with offset arrays leads to {ref,ref,..., ref} where ref are cell references. This {ref,ref,..., ref} will not be evaluated correctly. N gets the number from the ref.

So your formulas will be:

=SUMPRODUCT($C$12:$G$12,N(OFFSET($AB$12,$AL$13,-(COLUMN($C:$G)-COLUMN($C:$C)))))

or

=SUMPRODUCT(N(OFFSET($G$12,0,-COLUMN($C:$G)+COLUMN($C:$C))),OFFSET($X$12,$AL$13,0,1,5))