Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 11, 2024

Another Spilled Array connundrum. Variant of a previous discussion that was solved.

Follow on from

https://techcommunity.microsoft.com/t5/excel/another-dynamic-spilled-array-challenge-multiply-2-spilled/m-p/4263784

 

In this situation, I am trying to extend the concept of a previous discussion that was expertly solved by an esteemed contributor here.

 

Best illustrated by the attached workbook which also shows the previous version with its best solution.

Look at Revision 2 for this one. The situation in this version is that I want to multiply the various rows in the spilled array not by a single other range (spilled array) by a matched row in collection of ranges (spilled arrays) based on the driver.

 

So if the driver for row 1 is Headcount then I want to multiply that row by the relevant row in the Headcount array (relevant row is determined by the Entity). Another row in the main table may have the driver % Net Revenue  so I will multiply that row by the Net revenue table (selecting the appropriate entity row) and then by the Growth factor.

 

Thanks in advance.

 

 

excel
Formulas and Functions
  • djclements's avatar
    djclements
    Oct 13, 2024

    James_Buist Sure, if there's potentially other data in between the headcount sections, you could just VSTACK the applicable data instead of using SCAN:

     

    =LET(
    lookup_val, B15:B21 & "|" & F15:F21,
    lookup_arr, VSTACK(B4 & "|" & F5:F6, B7 & "|" & F8:F9, B10 & "|" & F11:F12),
    return_arr, VSTACK(I5:M6, I8:M9, I11:M12),
    factor, E15:E21,
    values, I15:M21,
    IFNA(INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))),1)*values*factor
)

     

    It's also possible to use the secondary syntax of INDEX and make use of the area_num argument; however, it will depend on the consistency of your headcount data (each area must have the same "entities" appearing in the same order, and all "codes" aside from 1 must be included):

     

    =LET(
    lookup_val, F15:F21,
    lookup_arr, F5:F6,
    codes,  B15:B21,
    driver, D15:D21,
    factor, E15:E21,
    values, I15:M21,
    IF(driver="Fixed",1,IFERROR(INDEX((I5:M6,I8:M9,I11:M12),XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(values)),codes-1),0))*values*factor
)

     

    It's cleaner, but also easier to make a mistake if you have many entities/codes. 😉

  • djclements's avatar
    djclements
    Bronze Contributor
    Oct 12, 2024

    James_Buist Building off the previous revision, it could be something like this:

     

    =IF(D15:D21="Fixed",1,IFNA(INDEX(I4:M12,XMATCH(B15:B21&"|"&F15:F21,SCAN("",B4:B12,LAMBDA(a,v,IF(v="",a,v)))&"|"&F4:F12),SEQUENCE(,COLUMNS(I4:M12))),0))*I15:M21*E15:E21

     

    Or, for improved readability:

     

    =LET(
    lookup_val, B15:B21 & "|" & F15:F21,
    lookup_arr, SCAN("",B4:B12,LAMBDA(a,v,IF(v="",a,v))) & "|" & F4:F12,
    return_arr, I4:M12,
    driver, D15:D21,
    factor, E15:E21,
    values, I15:M21,
    IF(driver="Fixed",1,INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))))*values*factor
)

     

    Also, since the "factor" now shows 100% for the "fixed" items, you might be able to omit the "driver" and remove the IF statement altogether:

     

    =LET(
    lookup_val, B15:B21 & "|" & F15:F21,
    lookup_arr, SCAN("",B4:B12,LAMBDA(a,v,IF(v="",a,v))) & "|" & F4:F12,
    return_arr, I4:M12,
    factor, E15:E21,
    values, I15:M21,
    IFNA(INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))),1)*values*factor
)

     

    However, I'm not sure if there would be other instances in your actual dataset where no match is found, so this "catch-all" approach to handle #N/A errors might not be what you want. I've put both methods in the attached file. Try them and see...

    • James_Buist's avatar
      James_Buist
      Brass Contributor
      Oct 12, 2024
      Had a cursory look. Clever. Have a long flight early tomorrow and biz trip to US for a few days. i hope to have proper look sometime next week and look to incorporate into the model.

      One comment though, If I have a number of sections in the return_arr, then perhaps it would be better just to stack these into a single array and use that rather than the actual range which may have other stuff added between. So getting a cleaner return_arr with just the data sections stacked.
      I'll have a go when I get test implementation
      • djclements's avatar
        djclements
        Bronze Contributor
        Oct 13, 2024

        James_Buist Sure, if there's potentially other data in between the headcount sections, you could just VSTACK the applicable data instead of using SCAN:

         

        =LET(
    lookup_val, B15:B21 & "|" & F15:F21,
    lookup_arr, VSTACK(B4 & "|" & F5:F6, B7 & "|" & F8:F9, B10 & "|" & F11:F12),
    return_arr, VSTACK(I5:M6, I8:M9, I11:M12),
    factor, E15:E21,
    values, I15:M21,
    IFNA(INDEX(return_arr,XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(return_arr))),1)*values*factor
)

         

        It's also possible to use the secondary syntax of INDEX and make use of the area_num argument; however, it will depend on the consistency of your headcount data (each area must have the same "entities" appearing in the same order, and all "codes" aside from 1 must be included):

         

        =LET(
    lookup_val, F15:F21,
    lookup_arr, F5:F6,
    codes,  B15:B21,
    driver, D15:D21,
    factor, E15:E21,
    values, I15:M21,
    IF(driver="Fixed",1,IFERROR(INDEX((I5:M6,I8:M9,I11:M12),XMATCH(lookup_val,lookup_arr),SEQUENCE(,COLUMNS(values)),codes-1),0))*values*factor
)

         

        It's cleaner, but also easier to make a mistake if you have many entities/codes. 😉

Resources