Is there a way to manipulate multiple data values in one cell without splitting the values apart?

Question

I have multiple "Mixes" that I want to create a cost for. Currently each mix is shown in component form in a cell for clarity, the number of units is in a separate column within the table and the cost per unit is broken out into individual components in a seperate sheet in the workbook.

Example On Sheet 1, I have the following: Cell C2 is "3" (# of units), C3 is "2" and C4 is "4". Cell D2 is "1 cup flour, .25 cup milk, 1 egg" Cell D3 is "2 cup flour, .33 cup oil, 2 egg" Cell D4 is "1 cup flour, 1 egg" I want column E to have a formula to calculate cost based on prices in sheet 2 that has cost for Flour in Cell B2 as ".05", cost for milk in Cell B3 as ".08", cost for egg in Cell B4 as ".10" and cost for oil as ".04". I also have a generic mixing cost of ".25" that I want to apply per unit that is not called out anywhere other than in Sheet 2 cell B16.

Currently I am manually configuring each formula in Column E which is time consuming and leads to the potential of error. An example of this for cell E2 is "=3*(1*.05+.25*.08+1*.1+.25)"

Two options I have thought of is to create a Mix Column that shows Mix 1, Mix 2 and Mix 3 that I then define a per unit cost that I can multiply by and create a unique mix for each combination (Currently around 15 Mixes and growing). Example Column F is the Mix Cost so the formula in E2 becomes "=C2*F2". My question is can I use a IF() Statement to populate this column automatically based on the data in Column D as I will want Column F hidden when printing data and it would also be easier to navigate the sheet if the column is hidden when entering data.

The other option is to create a formula that splits column D into individual components and then multiply using the individual values and then hide all of the individual column data and the formula would read something such as "=Units*(data point 1+data point 2+data point 3+mixing data point)". My concern with this approach is that when I calculate the mix values 3-4 times per Row and if I begin to have 5-6 ingredients the sheet will get very large and clumsy to navigate.

Any other ideas or approaches would be helpful.

Answer 1

Your description really is cumbersome,
but I believe the following will give you an idea on how one can do it...

--- save as .csv file and open in excel or libreoffice ---

,,,,
,Item A,Item B,Item C,Sum
,100,10,1,
,,,,
Mix,,,,
"=DEC2BIN(ROWS($A$6:A6),COLUMNS($B$2:$D$2))","=VALUE(MID($A6,COLUMNS($B$2:B$2),1))","=VALUE(MID($A6,COLUMNS($B$2:C$2),1))","=VALUE(MID($A6,COLUMNS($B$2:D$2),1))","=SUMPRODUCT($B$3:$D$3,B6:D6)"

--- end of file ---

"Copy down" line 6 (last line) to make seven lines (six copies).

Change the numbers on row 3 and see what happens.

The table below "Mix" does this;
Column A - create a binary number based on row-position (001 for first row, 111 for 7th row).
Columns B, C and D - pick out the binary digit in the position corresponding to the column, make it be a "number" so we can do calculation on it.
Column E - use SUMPRODUCT() to calculate the "mix"-cost.

SUMPRODUCT(Array1, Array2, ...)
takes the first element in Array1 and Array2, multiplies them, does the same with element 2 and adds that, then element 3, and so on...

It can handle a number of arrays, not just two, there is a limit though.

The csv file above has the third row as array of "cost", then uses rows starting at row 6 as "selection" arrays, to create the seven possible mix-costs for the three items (2 to the power of three rows, for three items, with at least one chosen).

*Formulas:
A column can be changed to handle more items just by changing the COLUMNS argument.
B column can be copied to "any" number of columns (three present in the csv file).
"any" is limited by "DEC2BIN" in column A though, it can only produce ten digits in Excel.
Column E, needs adjustment too, accordingly - I'll leave that as a final task to complete on your own; not very hard really.

When you have this sixth row, you can copy it down for the required number of rows, the last one will need to have all "1"-s in the mix-table.