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
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.
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])
)
)
)
[GOAL ...] / [TOTAL ...]
computes the maximum allowed quantity of the given metric (...
).
LIST(..., ..., ..., ...)
creates a List from the four computed maximum quantities (...
; see (1)). A List is required my the MIN() function (see (3)).
MIN(...)
selects the lowest of the computed maximum quantities (...
; see (2)).
FLOOR(...)
converts the lowest computed maximum quantity (...
; see (3)) into a whole number.
AWESOME! I will get to work on this! Thank you so much!
User | Count |
---|---|
60 | |
25 | |
13 | |
12 | |
6 |