What If Analysis

AppSheet AppSheet Q&A
1

This may require more playing with the new INTELLIGENCE feature but I’ve been stumped for quite some time on a solution to the following issue which seems to be pretty simple.

I have a Nutrition app where I want to auto determine the quantity of each of 3 ingredients in order to meet the predetermined number of calories, protein, fat and carbs for a meal.

For example:

MEAL 1 needs
350 Calories [GOAL CAL]
30 grams of Protein [GOAL PRO]
10 grams of Fat [GOAL FAT]
40 grams of Carbs [GOAL CARB]

I want the user to be able to select from any of the ingredients I have (REF from another table with said ingredient’s cal/pro/fat/carb) and then
AUTO CALCULATE the Quantity of that ingredient to give a total that equals my MEAL 1 requirements.

In the past I set the [QUANTITY of ingredient 1] column VALID IF to be [TOTAL CAL]<[GOAL CAL] and then I manually increased the quantity to the highest allowed value.

There has to be a way to select the MAX value without going over…Help Please

2

Calculate Quantity of a Single Goal

Given:

([QUANTITY] * [TOTAL CAL]) <= [GOAL CAL]

Solve for [QUANTITY].

Divide both sides by [TOTAL CAL]:

([QUANTITY] * [TOTAL CAL] / [TOTAL CAL]) <= ([GOAL CAL] / [TOTAL CAL])

Simplify:

([QUANTITY] * 1) <= ([GOAL CAL] / [TOTAL CAL])

Simplify:

[QUANTITY] <= ([GOAL CAL] / [TOTAL CAL])

Therefore, ([GOAL CAL] / [TOTAL CAL]) would give the maximum quantity allowed within the goal (strictly speaking, it would give you the exact quantity needed to exactly meet the goal). Optionally instead use FLOOR([GOAL CAL] / [TOTAL CAL]) if you only want the largest whole quantity (e.g., 2 instead of 2.35) allowed within the goal.

Calculate Quantity for All Goals

To identify the largest whole quantity that meets all four goals:

FLOOR(
  MIN(
    LIST(
      ([GOAL CAL] / [TOTAL CAL]),
      ([GOAL PRO] / [TOTAL PRO]),
      ([GOAL FAT] / [TOTAL FAT]),
      ([GOAL CARB] / [TOTAL CARB])
    )
  )
)

  1. [GOAL ...] / [TOTAL ...] computes the maximum allowed quantity of the given metric (...).

  2. LIST(..., ..., ..., ...) creates a List from the four computed maximum quantities (...; see (1)). A List is required my the MIN() function (see (3)).

  3. MIN(...) selects the lowest of the computed maximum quantities (...; see (2)).

  4. FLOOR(...) converts the lowest computed maximum quantity (...; see (3)) into a whole number.

2 Likes
3

AWESOME! I will get to work on this! Thank you so much!

1 Like