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.

Use Action to retrieve or update data in an unrelated entity in Dynamics 365 Workflows

There are scenarios where you need to access data from an entity that is not directly related to the entity for which you are building a Workflow in Dynamics 365.

For example, you want to send an email alert to the Owner of the Account on the Opportunity when an Opportunity Line is created or updated with the amount exceeding $10,000. In this case, the Workflow will be created for the Opportunity Line entity which triggers the Workflow. However, within the Workflow Designer, you can access data only from the entities that are reated to Opportunity Line entity such as Opportunity or Product. However, Account is not directly related to the Opportunity Line entity and therefore you cannot access the Owner field of the Account within the Workflow. To get around this limitation, you can create another Process of type Action. The Action will be for the Account entity and it will return the Entity Reference for the Account being passed to the Action. You can then call (perform) this Action within your Workflow. This will enable you to access any fields from the Account entity within the Workflow. Here are the specific steps to implement this solution.

Create a Process of type Action on the Account entity.

Add an Argument of Type EntityReference returning the Account entity as Output.

Add the Assign Value step as shown in the screenshot below.

Activate the Action.

Next, create a Workflow for the Opportunity Line entity.

Select "Record is created" and "Record fields change" (Line Amount) triggers.

Add condition. In this case it is: if Opportunity Line:Amount > 10,000.

Add the Perform Action step and select the Action created above.

Pass the Account on the Opportunity as the Input parameter.

Add the Send Email step.

As you can see from the screenshot below, you can now access the Owner field on the Account to populate the “To” (email recipient) in the Send Email step.

Complete the other details for your email.

Activate the Workflow and test your Workflow by adding or updating a line on the Opportunity exceeding $10,000.

You can use this Action in other Workflows or code where you need to retrieve data from a specific Account record.

In addition, you can use this technique to update an unrelated record in your Workflow which was not previously possible.

You can also use this technique to retrieve data from the Business Unit of the record owner
or Field Service Settings which are not directly related to the record on which the Workflow is being executed.

In this post, we saw how you can retrieve or update data in an unrelated entity within the Workflow using Action.

Find Resources using Map View

One of the common scenarios for schedulers/dispatchers using Dynamics 365 Field Service solution involves finding a technician to attend to an urgent or emergency service call. Resources who are in the area close to the service location are preferred due to urgency of the call. If the resources who are in the area are busy i.e. booked for routine Work Orders, they do not show up as “available” when using Schedule Assistant. How do you find the resources close to the service location who may be booked for lower priority routine work orders?

The Map View feature of the Schedule Board makes this possible. Once you locate the nearby resources, you can reschedule them from lower priority routine Work Orders and assign them to urgent Work Orders.

Here are the steps to accomplish this.

1. Navigate to Schedule Board
   
   Field Service > Schedule Board

2. Click on the Map View tab under Filter and Map View on the left-hand side of the Schedule Board

3. Select Resources
   
   Select resource you want to see on the Map by clicking on individual pin next to the resource or select all resources by clicking on the icon at the top.
   
   

4. Search for the Requirement (Work Order)
   

Click on the Search (magnifier glass) icon.

Enter the Work Order Number (resource requirement) for which you want to find available resources, press enter and click on Add.


You can even create a new Resource Requirement from within this form.

5. Locate resources on the map
   
   The Requirement you selected is shown as a pin with a “?” and a circle under it. Resources are shown as solid colored pins. You can hover over the pin to see the name of the resource. You can use the + or – buttons to zoom in or out on the map.