Calculation on an Array of Numbers

Use Case: I have a recipe app that requires the user to first build an ingredient database. When adding an ingredient, they’ll be asked whether it is to be measured by Volume, by Weight, or as an Individual Item [i.e. unitTYPE]. They will also be asked for packageSIZE (mL if by Volume, gram by weight, etc.) and packageCOST.

Once a user is ready to create a recipe, they’ll select the ingredient, measurement quantity, and unit (from a list of child units under parent unitTYPE), then the app will calculate the cost of that recipe based on the cost of its ingredients which is based on the cost of a converted ingredient unit.

The conversion is where I’m struggling.

I have Sheet UNITS which has a conversionNUMBER column and I have Sheet INGREDIENT which holds the column packageSIZE. I have also used Rel/Lookup in INGREDIENTS to pull the Array of conversionNUMBERS that corresponds with the units of the selected unitTYPE.

I know how to pull out each single value from the Array and pass it through a formula, but I’d need a separate column (two columns, I think) for each Array Index, the quantity of which changes from unitTYPE to unitTYPE and may also change as I add more units / conversionNUMBERS. Then I have to do new columns again to calculate costs.

Ideally, I would like to pass a Math formula [packageSIZE / conversionNUMBER] through each number in the lookup Array, and have it autimatically output a new Array (or at least a csl). Then I’d run a similar formula with packageCOST on the new Array.

Is this possible? Or any other tricks to accomplish this goal?

Thank you!

Can you show us some examples of the data, and data structure you’re working with? Thank you.

1 Like