CODEWITHSUNDEEP
excelexcel-formulaoffice365array-formulas
ciso
ciso

Reputation: 3050

Using Office365 Excel array formulas, how to convert this standard formula?

These two formulas are the same, except the first one is not an array formula and the second one is. How can the first formula be converted to an array formula? Getting circular logic when using the array formula.

Standard Formula (works fine, no circular logic):

=LET(a, A2, b, B2, c, C2, d, D1, e, E1,
    dd, IF(a = 1, 0, d),
    ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd)  )

Array Formula (circular logic error):

=LET(a, A9:A12, b, B9:B12, c, C9:C12, d, D8:D11, e,E8:E11,
    dd, IF(a = 1, 0, d),
    ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd)  )

The formula is a fairly simple weighted average of two sets of numbers. Trying to convert it to an array formula. The previous result is used on the following row, except on the first row where there is no previous result.

The difficulty is in how to reference the previous result cell when calculating the current cell.

enter image description here

Data:

Seq B C D
1 100 1.00 -
2 100 3.00 800
3 250 2.00 200
4 400 5.00 300

Upvotes: 1

Views: 88

Answers (2)

VBasic2008
VBasic2008

Reputation: 54898

Accumulate Using SCAN With a Shift

  • Lose the last row (DROP) of the last column (TAKE) and stack a zero on top of it (VSTACK):

    VSTACK(0,DROP(TAKE(data,,-1),-1))

=LET(data,B2:D5,
    dt,HSTACK(DROP(data,,-1),VSTACK(0,DROP(TAKE(data,,-1),-1))),
SCAN(0,SEQUENCE(ROWS(dt)),LAMBDA(sr,r,
    LET(b,INDEX(dt,r,1),c,INDEX(dt,r,2),d,INDEX(dt,r,3),
(b*c+d*sr)/(b+d)))))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152605

INDEX each array and use SCAN to return the values:

=LET(
    a, A9:A12, 
    b, B9:B12, 
    c, C9:C12, 
    d, D8:D11,
    dd, IF(a = 1, 0, d),
    SCAN(0,a,LAMBDA(z,y,(INDEX(b,y)*INDEX(c,y)+INDEX(dd,y)*z)/(INDEX(b,y)+INDEX(dd,y)))))

enter image description here

Upvotes: 1

Related Questions