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 = (P5*a1 + P4*a2 + P3*a3 + P2*a4 + P1*a5)/L
The Second formula has RL in it but suffers from the same problem.
M = RL * x - P5(x-a1) - P4(x-a2) - P3(x-a3) - P2(x-a4) - P1*(x-a5)
When I look at these I see a SUMPRODUCT.
My P1...P5 values are layed out in the same order in the range $C$12:$G$12 Or named range Axle_P.
My a1...a5 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:
(P1*a1 + P2*a2 + P3*a3 + P4*a4 + P5*a5)
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 Pn-i+1 to 0 if ai 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.
See Question&Answers more detail:
os