Forum Discussion

Nishkarsh31's avatar
Nishkarsh31
Brass Contributor
Apr 11, 2021
Solved

How to sum an array, after applying a few conditions to it? It's complicated, I'm attaching a file.

I'm attaching a file. It's self explanatory.

I wanna convert the array to be summed base on a different array and then do the final summation.
Is it possible to achieve this without adding a helper column?

PeterBartholomew1 SergeiBaklan Riny_van_Eekelen 

15 Replies

  • Nishkarsh31 

    Glad to have at least contributed some ideas.

    The attached file uses index to identify the columns for the ingredients, quantity and units, but the main difference is that it encloses the entire formula within a Lambda function which it then names as SUMMARY.  That makes SUMMARY(1) the summary data for recipe 1 etc.

     

    This is no help whatsoever to you right now, unless you happen to be on the 365 insiders beta channel, but it should work some time in the future when the Lambda functionality is rolled out.

    • Nishkarsh31's avatar
      Nishkarsh31
      Brass Contributor
      Yes, I'm too waiting for lambda to be rolled out on regular Microsoft 365.
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    Nishkarsh31 

    My idea would be to use the CONVERT() function. But that requires to change "Lt" to "l" and "Kg" to "kg".

    The next step would be to unpivot your data into a flat table.

    Next steps beyond spreadsheet formulas would be Power Query or Power Pivot.

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      Detlef_Lewin 

      I like the CONVERT idea; it is a function that I have never used as an array operator.

      = LET(
            unitCost, XLOOKUP(Recipe[Recipe1], Ingredient[Ingredient], Ingredient[Cost]),
            ingredientUnits, XLOOKUP(Recipe[Recipe1], Ingredient[Ingredient], Ingredient[Unit]),
            cost,  CONVERT(+Recipe[Qty1], +Recipe[Unit1], ingredientUnits) * unitCost,
            dry, "Dry " & SUM(SUMIFS(Recipe[Qty1], Recipe[Unit1],{"kg","g"}) * {1,0.001})&" kg",
            wet, "Wet " & SUM(SUMIFS(Recipe[Qty1], Recipe[Unit1],{"l","ml"}) * {1,0.001}) & " l",
            total, SUM(cost)&" INR",
            TEXTJOIN(ΒΆ,,dry,wet,total))

      I am less convinced by evolution path you plot from spreadsheet to Power Query.  There are transformations that used to require PQ that can now revert quite happily to formulas; not the heavy database stuff but, rather, the smaller interactive calculations (1000x100 arrays rather than 100,000 records x 100 fields with a few inner joins).

       

       

      • Nishkarsh31's avatar
        Nishkarsh31
        Brass Contributor

        PeterBartholomew1 

        Hi sir, the formula looks good. There's minor problem

        1) If There's one ingredient missing the formula doesn't work. There would be cases, we might not use one ingredient. How can we work around that?

        2) Also, instead of giving table reference, I want to give array reference since I'll be adding a lot of columns and the expanded table won't automatically change the column names.
        (I know it would make the table use redundant, but can't help it)

        3)Can you let me know how to put that delimiter in textjoin to skip a line, where to find it on keyboard?

        4) What would you recommend, Switch or Convert in the final formula?

  • Nishkarsh31 

    A formula for the cost could be

    = LET(
        unitCosts, 
          Ingredient[Cost] * SWITCH(Ingredient[Unit],"kg",1,"g",1000,"lt",1,"ml",1000),
        qty, Recipe[Qty1] / SWITCH(Recipe[Unit1],"kg",1,"g",1000,"lt",1,"ml",1000),
        cost, qty * XLOOKUP(Recipe[Recipe 1],Ingredient[Ingredient],unitCosts),
      SUM(cost)&" INR")

    The weight and volume of dry and liquid ingredients are given by

    = SUM(SUMIFS(Recipe[Qty1], Recipe[Unit1],{"kg","g"}) * {1,0.001})& " kg"
    
    = SUM(SUMIFS(Recipe[Qty1], Recipe[Unit1],{"lt","ml"}) * {1,0.001}) & " lt"

    respectively.

     

    You could always try the LAMBDA function so that the same formula can be applied to recipe1 and recipe2, but that is probably several steps too far.

  • Nishkarsh31 

    It is quite possible but ...

    1. You must perform the summation before converting multiple measures to text

    2. You cannot use OR to evaluate an array of Boolean operations because it combines the entire array

        (plus '+' provides a workaround)

    3. I do not recommend adding Kg and Lt unless you know the density of the ingredients. 

     

    Are you able to use the LET function to make sense of your calculation?

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      Detlef_Lewin 

      My argument would be "do you need to see the value?", "why is it important to you?"  If there is no reason to see the intermediate values, then they merely constitute 'sheet junk', in much the same manner as extraneous ornamentation of charts is described a 'chart junk'.

       

      Despite that, there are reasons of computational efficiency that might justify creating an array of intermediate results.  Also, many users like to be able to check Excel calculations with a pocket calculator. For me, that is mis-directed effort.  They would be better advised to create readable formulas and check those, because errors are (virtually) always to be found in the users' code, not the Excel evaluation.

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        PeterBartholomew1 

        Very often intermediate results are re-used multiple times. So a single formula tends to get bigger and takes more calculation time.

        I know there is now LET()  but I am not getting warm with it - yet.

         

        If you really want to build a single formula you have to build helper columns first and then try to put them together.

         

Resources