Excel Fun!

Last Friday’s Morning Brew newsletter contained the following math puzzle.

Friday puzzle

In a small village, 90% of people drink tea, 80% coffee, 70% whiskey, 60% gin. Nobody drinks all four. What percentage of people drink alcohol?

The puzzle looks deceptively simple but it takes some work to solve it.

It is possible to solve the puzzle using logic and math. However, I wanted to find out if I could use Microsoft Excel to solve the puzzle with the help of the Solver add-in.

If you like solving math puzzles, you may want to pause here and attempt to solve the puzzle on your own. Otherwise, read on.

This is how I set up the puzzle in Excel for the Solver to solve.

The table contains the drinks (tea, coffee, whiskey and gin) as rows and ten 10% segments of the population as columns of the table. Each cell ($B$2:$K$5) in the table contains 1 if the population segment takes the drink and 0 if it does not.

We are now ready to set up the Solver Parameters.

We know the following:

1. Percentage of tea drinkers is 90%, coffee 80%, whiskey 70% and gin 60%.

2. Nobody drinks all four drinks i.e. none of the 10% segments in the table drinks more than 3 drinks.

Based on this information we can set up the Solver Parameters as follows:

Objective:

Assuming that there is only one solution, you can select either Min or Max for the Percentage of alcohol drinkers (cell $L$9). You can also try both options to ensure that the solution is unique.

By Changing Variable Cells:

Contents of the table i.e. $B$2:$K$5. Solver will try changing values in these cells to find the solution that meets the constraints below.

Subject to the Constraints:

1. Each cell in the table must be either 0 or 1 i.e. Binary.

2. Sumproduct for each row must be equal to the specified percentage (90%, 80%, etc.).

3. Total of each column must be less than or equal to 3 (because nobody drinks more than 3 drinks).

Solving Method:

Evolutionary

For the explanation of Solving Methods, refer to: Excel Solver: Which Solving Method Should I Choose? - EngineerExcel

Click “Solve” to solve the problem.

You can view the solution in the screenshot above. Green cells indicate drinkers and red cells indicate non-drinkers. Cell $L$9 shows that the percentage of people that drink alcohol is 100% (the answer to the puzzle) i.e. everyone in the village drinks alcohol. 30% drink both whiskey and gin and the remaining 70% drink one of the two.

You can download the Excel workbook here.

Note: You will need to load the Solver add-in to view the Solver Parameters and solve the problem.

I would love to hear about any math or stats puzzles you have solved using Excel!

Workaround for passing TABLE reference to Excel LAMBDA function

You can use the new LAMBDA function in Excel (Desktop edition) to create custom, reusable functions and call them by a friendly name. The custom function is available throughout the workbook and can be called like native Excel functions.

The Excel Labs (formerly Advanced Formula Environment) add-in’s Import from Grid feature can be used to create the LAMBDA function from an example you create.

For the purpose of this article, I wanted to create a LAMBDA function to calculate the financial ratio (Receivables Turnover ratio for this example) from the Trial Balance table exported from Quickbooks Online. However, any Trial Balance table can be used as long as it follows the format used for this table. The following screenshot shows the part of the table used in this example.

The function takes the Financial Year as a parameter and calculates the Receivable Turnover ratio as follows:

Receivable Turnover = Revenue / Average of Beginning and Ending Accounts Receivable

I started by building my example (B2:E2) using the SUMIFS function as shown in the screenshot below.

I then used the Excel Labs add-in’s Import from Grid feature to build the LAMBDA function.

However, the add-in produced the following error because the Table reference cannot be used in the LAMBDA function.

A workaround for this limitation is to use the INDIRECT function to reference the required tables and columns in the LAMBDA formula. I changed the formulas as shown in the screenshot below.

=SUMIFS(

INDIRECT("TrialBalance[Balance]"),

INDIRECT("TrialBalance[Financial Year]"),A2,

INDIRECT("TrialBalance[Account Type]"),"Accounts Receivable (A/R)"

)

Clicking the Preview button generated the following code with a warning:

The warning is related to the INDIRECT function. The INDIRECT function is volatile because the range of the table is unknown and can change. As such it may cause the entire workbook to be recalculated frequently. Volatile functions can impact performance, especially for large workbooks. You can set the "Manual" calculation option to avoid automatic recalculation.

Here is the complete LAMBDA function generated by the add-in:

RecvTO = LAMBDA(FinYear,

    LET(

        EndAR, SUMIFS(

            INDIRECT(

                "TrialBalance[Balance]"

            ),

            INDIRECT(

                "TrialBalance[Financial Year]"

            ), FinYear,

            INDIRECT(

                "TrialBalance[Account Type]"

            ), "Accounts Receivable (A/R)"

        ),

        BegAR, SUMIFS(

            INDIRECT(

                "TrialBalance[Balance]"

            ),

            INDIRECT(

                "TrialBalance[Financial Year]"

            ), FinYear - 1,

            INDIRECT(

                "TrialBalance[Account Type]"

            ), "Accounts Receivable (A/R)"

        ),

        Revenue, SUMIFS(

            INDIRECT(

                "TrialBalance[Balance]"

            ),

            INDIRECT(

                "TrialBalance[Financial Year]"

            ), FinYear,

            INDIRECT(

                "TrialBalance[Account Type]"

            ), "Income"

        ) * -1,

        Revenue /

            ((EndAR + BegAR) / 2)

    )

);

You can verify your custom function by entering it in any cell. See the F2 cell below containing the custom function: =RecvTO(A2).

You can download the above Excel workbook here. Note that you will need to install the Excel Labs add-in to use the Import from Grid feature.