Search code examples
excelexcel-formulaarray-formulas

Can I use the LET formula to define an array in Excel?

I've got a formula that verifies that a list of totals all match. It involves comparing the maximum value and the minimum value, and if they match all values must be the same. I'd like to remove the repeating list of totals within each max and min.

Here's the formula.

=MIN(SUM(Table1[Count]),SUM(Table2[Count]),SUM(Table3[Count]))=MAX(SUM(Table1[Count]),SUM(Table2[Count]),SUM(Table3[Count]))

Can I use LET to define the list of totals as an array?

=LET(Totals,{SUM(Table1[Count]),SUM(Table2[Count]),SUM(Table3[Count])},MIN(Totals)=MAX(Totals))
Solution

  • Let me know if either of the two works:

    =LET(X,CHOOSE({1,2,3},SUM(Table1[Count]),SUM(Table2[Count]),SUM(Table3[Count])),MIN(X)=MAX(X))

    Or:

    =LET(A,SUM(Table1[Count]),B,SUM(Table2[Count]),C,SUM(Table3[Count]),MIN(A,B,C)=MAX(A,B,C))

    I wrote both without any sample data, so they are untested.