Forum Discussion

Jalal_1988's avatar
Jalal_1988
Brass Contributor
Jul 07, 2020
Solved

Excel Challenge

I have big problem in writing  Excel formula for the following problem: I have Three feature of my costumer I want to separate my costumers by these three features and give them Index 1, 2 and 3, ...
excel
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jul 07, 2020

    Jalal_1988 

    =MMULT(--(COUNTIFS($B$2:$B$17,B2,$A$2:$A$17,{"A","B"})>0),{1;2})
mathetes's avatar
mathetes
Gold Contributor
Jul 07, 2020

Detlef_Lewin 

 

MMULT is a function I'd not seen before.

 

Frankly, the Help text on the function isn't very helpful in understanding it. Can you explain how it's working here to solve the question asked by @Jalal_1988

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jul 07, 2020

mathetes 

I'm not good at explaining MMULT(). So I have to refer to the support page. The key text is: The result is an array with the same number of rows as array1 and the same number of columns as array2.

array1 has one row (and two columns) and array2 has one column (and two rows). So the output is an array with one row and one column.

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 07, 2020

    Detlef_Lewin  that is a clever solution

    mathetes  mmulti() stands for matrix multiply which goes back to linear algebra days.  

    I started to try and explain it here but you would be much better served with a website designed to teach it like https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwj2y6HcmrzqAhUFLs0KHfvBDbUQFjAAegQIAhAB&url=https%3A%2F%2Fwww.mathsisfun.com%2Falgebra%2Fmatrix-multiplying.html&usg=AOvVaw2H0BMrqG1AOypxo5VF-pz8

    • mathetes's avatar
      mathetes
      Gold Contributor
      Jul 08, 2020

      mtarler 

       

      Thanks for that link. I'm printing the page now for careful study.....and then I'll keep my eyes open for opportunities to apply MMULTI. It's in the using that it'll really (finally) make sense.

      • JMB17's avatar
        JMB17
        Bronze Contributor
        Jul 08, 2020

        mathetes 

         

        One use can be to create an array of subtotals for use in an array formula or lookup formula where, for whatever reason, you don't/can't have a subtotal helper column. So, if you had numbers in A1:A10, and entered this in B1:B10, you would get a subtotal array (credit to Harlan Grove):

        =MMULT(--(ROW(A1:A10)>=TRANSPOSE(ROW(A1:A10))),A1:A10)

         

        I'm not an expert on it's use, but I've used it in situations where I needed an OR criteria in an array calculation, but it had to be condensed into a single dimension array (instead of leaving it 2D and multiplying it out-sometimes the other parts of the formula were also OR criteria with different dimensions and the normal array multiplication wouldn't work and MMULT was more compact than the usual array addition I would use for OR). 

         

        Here's an example file of using mmult on a 2D data table with multiple OR criteria in both dimensions. 

         

         

         

         

Resources