Is it possible to extend a dynamic array with calculations in the extended columns?

[larryf]

Hi Friends,

I’ve been transitioning my work to ‘new’ Excel, taking advantage of dynamic arrays, the Let function, lambdas, etc. It’s a spectacular improvement to a product I’ve been using since it started life as Multiplan. I’m currently exploring the bounds between what I know is possible with these tools and what is beyond Excel’s current capabilities.

Let’s assume that we’re dealing with a plumbing equipment distributor who record in a table (Raw Invoice Data Table) the data found in columns A through E in my example workbook. The way that the distributor works is to record sales at a fixed base price by SKU (columns G-H), common for all customers. At the end of the quarter, they rebate back to each customer an amount that is dependent on the customer’s contract and who the manufacturer of the product is (columns J-M).

I can easily write a formula in O5 that will spill the entire contents of the Raw Invoice Data Table, and I can modify that formula to only spill selected columns from the Raw Invoice Data Table. What I’m having trouble with—and don’t even know if it’s possible—is to write a single formula that will spill data from the Raw Invoice Data Table and append columns it to that have calculations in them.

In the attached example, the end product I’m seeking would look like what I’ve labeled REPORT in columns O through V. Columns O through S would just be the spilled Raw Invoice Data Table, Column T would be an appended column that looks up prices from the Base Price table, Column U would be an appended column that performs math (column T * column U), and Column V performs a lookup of Customer # and Manufacturer into the Quarterly Rebate Table and multiplies the returned rebate by the Extended Price (column U). My question is, is this achievable as a single formula in cell O5 that spills down and right?

Thanks in advance for your help!

 

[Peter Bartholomew]

Do you have HSTACK? Without it building the final report gets somewhat messy.

= LET(
      basePrice,       XLOOKUP(Invoice[Item], Price[Item], Price[Base Price]),
      extendedPrice,   basePrice * Invoice[Qty],
      rebatePercent,   Rebateλ(Invoice[Customer '#],Invoice[Manufacturer]),
      quarterlyRebate, extendedPrice * rebatePercent,
      report,          HSTACK(tblData,basePrice,extendedPrice,quarterlyRebate),
      SORT(report,1)
  )

where Rebateλ is defined to be

= LAMBDA(custm, supplier,
     LET(
        cutomerIdx,  XMATCH(Invoice[Customer '#], Rebate[Customer '#]),
        supplierIdx, XMATCH(
            Invoice[Manufacturer],
            Rebate[[#Headers],[Superior Plumbing]:[American Plumbing]]
        ),
        INDEX(tblRebate, cutomerIdx, supplierIdx)
     )
  );

 

[Excel Wizard]

Please try


=LET(z,tblData,bp,VLOOKUP(INDEX(z,,3),tbl_Price,2,),ep,bp*INDEX(z,,5),
qr,INDEX(tblRebate,MATCH(INDEX(z,,2),$J$5:$J$14,),MATCH(INDEX(z,,4),K4:M4,))*ep,
SORT(CHOOSE(SEQUENCE(,8),z,z,z,z,z,bp,ep,qr)))

or with the New HSTACK function

=LET(z,tblData,bp,VLOOKUP(INDEX(z,,3),tbl_Price,2,),ep,bp*TAKE(z,,-1),
qr,INDEX(tblRebate,MATCH(INDEX(z,,2),J5:J14,),MATCH(INDEX(z,,4),K4:M4,))*ep,
SORT(HSTACK(z,bp,ep,qr)))

 

[Peter Bartholomew]

=LET(z,tblData,bp,VLOOKUP(INDEX(z,,3),tbl_Price,2,),ep,bp*INDEX(z,,5),
qr,INDEX(tblRebate,MATCH(INDEX(z,,2),$J$5:$J$14,),MATCH(INDEX(z,,4),K4:M4,))*ep,
SORT(CHOOSE(SEQUENCE(,8),z,z,z,z,z,bp,ep,qr)))

I suspect you were right to include a version that would work without relying on the HSTACK beta release code. I had thought of using MAKEARRAY but I am particularly intrigued the way you introduce the columns of tblData within the CHOOSE function. VLOOKUP is a bit of a blast from the past though; I haven't used one of those for many years; even before XLOOKUP, I tended to veer towards INDEX/MATCH.

 

[larryf]

Thank you both, Peter and Excel Wizard. You're right: I don't have HSTACK on my production device, but tried it on my personal computer which is on the Beta Channel and it works like a charm. I can hardly wait for these new array and text functions to hit the 'regular' channels. In the meantime, Excel Wizard's non-HSTACK version provides comparable results and is just what I was looking for. This method will really help me overcome some obstacles I've hit in creating this report. I appreciate both of you!

 

[Peter Bartholomew]

This should be a working version based upon @Excel Wizard's use of CHOOSE, here wrapped in a Lambda function that can be reverted once HSTACK is generally available.

= LET(
    basePrice,       BasePriceλ(Invoice[Item]),
    rebatePercent,   Rebateλ(Invoice[Customer],Invoice[Manufacturer]),
    extendedPrice,   basePrice * Invoice[Qty],
    quarterlyRebate, ROUND(extendedPrice * rebatePercent, 2),
    report,          HStackλ(tblData,basePrice,extendedPrice,quarterlyRebate),
    SORT(report,1)
  )

where HStackλ is refers to

= LAMBDA(A₁,A₂,A₃,A₄,
    LET(
      k, SEQUENCE(1,8),
      CHOOSE(k,A₁,A₁,A₁,A₁,A₁,A₂,A₃,A₄)
    )
  )

One of my intentions of working with Lambda functions is to wrap complexity within modules so that any changes needed to refactor the code are limited in scope and can be implemented more easily.