Yes, while this expands the scope of decisions, it also falls straight to the trap I portrayed earlier: Multiple nested IF statements.
Which is exactly what I am trying to avoid.
Multiple IF statements require too many manual tweaking points, too many repetitions even though we adopt the "do it once, copy to them all" approach. Once something changes (either in the formulas themselves, and of course in the sheer number of said formulas) one needs to manually analyze, change and add to the formula. Also, according to what I read, there's a limit of 7 nested IF statement. And I never said I have only 4 such formulas.
Anyway - when I posted my first post I was under the assumption that the
EXCEL=>HTML thingy was
currently unavailable, and hence I should not use it. After your reply, I thought to actually google for it instead of relying only on what was stated in the forum guidelines. So now I may be able to provide a bit more insight as to my approach (and the
method I'm using has a part in what I'm trying to achieve, aiming at both
flexibility and
readability), what I expect, what goes wrong and exactly what I would like for it to do.
Back to the example:
I'm assuming an initial "Amount" of 1000 units for each category.
I'm also naming some standard 'set numbers'. Again, I would prefer not to use them in the formula directly so they may be changed at a later date.
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td><td style="background-color: rgb(192, 192, 192);">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">(Name)</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="3" style="background-color: rgb(192, 192, 192); text-align: right;">Usually being rotten:</td><td style="text-align: center;">25%</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rot</td><td>=' '!$H$2</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="3" style="background-color: rgb(192, 192, 192); text-align: right;">Special Deliveries:</td><td style="text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: center;">TruckLoad</td><td>=' '!$H$3</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="3" style="background-color: rgb(192, 192, 192); text-align: right;">Eaten by Ants:</td><td style="text-align: center;"> 2/5</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Ants</td><td>=' '!$H$4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="4" style="text-align: right;">=INDIRECT(ADDRESS(ROW(),2,3))</td><td style="background-color: rgb(192, 192, 192);">Amount</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td colspan="4" style="text-align: right;">=INDIRECT(ADDRESS(ROW(),3,3))</td><td style="background-color: rgb(192, 192, 192);">Delivered</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>[Late note: after the HTML code was issued and pasted, "Driver" was also defined and given the initial value of 10.]
Now, the formulas may change according the type of goods.
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 166px;"><col style="width: 117px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(255, 153, 204);">=Amount*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 255);">=(Amount/4)/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 0);">=Amount*Ants</td><td style="background-color: rgb(255, 153, 0);">=Amount*(1.3*Rot)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="background-color: rgb(153, 204, 255);">=Amount/4</td><td style="background-color: rgb(204, 255, 204);">=0</td></tr></tbody></table>
Note that the difference in the formulas doesn't even need to be precisely in 'Pairs'; While the yellow 'Pair' is common, it can combine a Yellow 'Rotten' formula with a different 'Delivered' one; and a purple 'Delivered' formula may be combined either with a Yellow formula or a Pink one.
Also note, that the Same 'Rotten' formula may take different results from the initial same amount: as the Yellow formula derives its data from the Delivery result, its numbers will be different for Oranges/Grapefruits than when applied for Watermelons/Apples. Also, on both cases of using the Purple formula (Bananas/Apples), the initial data for the Rotten formula will be different, as one will derive its calculations from the Delivered amount and the other from the Original amount.
Do not try to find any coherence in this scheme. There may be none. There may be others. The numbers may change. Formulas may be added.
Hence a solution which will involve a straight nested IF statement will prove itself to be both cumbersome and inefficient in the long run, and prone to much error on the first needed instance for manual updates.
(To complicate things even further we may change the Rot variable according to a lookup table which takes into account the time of the year. But aren't things complicated enough as they are?)
The straight numbers for the test case are as follows:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">244</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="text-align: right;">400</td><td style="text-align: right;">325</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="text-align: right;">250</td><td style="text-align: right;">0</td></tr></tbody></table>
Now, ideally, I would now regard this table as a 'Formula Template' table, and in the real table will point to the result in Delivered/Rotten columns for the correct calculation:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 269px;"><col style="width: 264px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>=INDEX(F.Del,MATCH($A12,$A$2:$A$8,0))</td><td>=INDEX(F.Rot,MATCH($A12,$A$2:$A$8,0))</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">500</td><td style="text-align: center;">(etc.)</td><td style="text-align: center;">(etc.)</td></tr></tbody></table>
(F.Del & F.Rot being Defined names for $C$2:$C$8 & $D$2:$D$8 ranges, respectively)
However, of course, the result takes its value directly from the result
given at the 'Template formula, and does not change upon changes in the Amount column.
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">500</td><td style="text-align: right;">976</td><td style="text-align: right;">244</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">8</td><td style="text-align: right;">250</td><td style="text-align: right;">0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td>
</td><td style="text-align: right;">400</td><td style="text-align: right;">325</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr></tbody></table>
So, obviously, I need a way of pointing the C & D cells to the actual
Formula rather than to a Formula
Instance, or should we say an
already-executed Formula.
I turned back to Names, thinking I can define a named Formula, then issue
that. As I already use and have defined names for the Amount & Delivered cells (not ranges, but relative cells - note the first table example above), I could define the following:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 166px;"><col style="width: 107px;"><col style="width: 28px;"><col style="width: 117px;"><col style="width: 107px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Formula</td><td style="background-color: rgb(192, 192, 192); text-align: center;">its Defined Name</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td><td style="background-color: rgb(192, 192, 192); text-align: center;">its Defined Name</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">D.Normal</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0);">=Amount/(1+Rot)</td><td style="background-color: rgb(255, 255, 0);">D.Normal</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(204, 153, 255);">D.Bananas</td><td>
</td><td style="background-color: rgb(255, 153, 204);">=Amount*Rot</td><td style="background-color: rgb(255, 153, 204);">R.Bananas</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 255);">=(Amount/Truckload)/(1+Rot)</td><td style="background-color: rgb(0, 255, 255);">D.Watermelons</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="background-color: rgb(204, 153, 255);">=Amount-(3*Driver)/(1+Rot)</td><td style="background-color: rgb(204, 153, 255);">D.Apples</td><td>
</td><td style="background-color: rgb(255, 255, 0);">=Delivered*Rot</td><td style="background-color: rgb(255, 255, 0);">R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="background-color: rgb(0, 255, 0);">=Amount*Ants</td><td style="background-color: rgb(0, 255, 0);">D.Kiwi</td><td>
</td><td style="background-color: rgb(255, 153, 0);">=Amount*(1.3*Rot)</td><td style="background-color: rgb(255, 153, 0);">R.Kiwi</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="background-color: rgb(153, 204, 255);">=Amount/TruckLoad</td><td style="background-color: rgb(153, 204, 255);">D.Bricks</td><td>
</td><td style="background-color: rgb(204, 255, 204);">=0</td><td style="background-color: rgb(204, 255, 204);">R.Bricks</td></tr></tbody></table>
And indeed, when taking my 'Template Table" and putting the appropriate Formula name
directly, it works:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">1000</td><td style="text-align: right;">800</td><td style="text-align: right;">200</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">250</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">1000</td><td style="text-align: right;">200</td><td style="text-align: right;">50</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">1000</td><td style="text-align: right;">976</td><td style="text-align: right;">244</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td style="text-align: right;">1000</td><td style="text-align: right;">400</td><td style="text-align: right;">325</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">1000</td><td style="text-align: right;">250</td><td style="text-align: right;">0</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>
Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=D.Normal</td></tr><tr><td>D2</td><td>=R.Normal</td></tr><tr><td>C3</td><td>=D.Normal</td></tr><tr><td>D3</td><td>=R.Normal</td></tr><tr><td>C4</td><td>=D.Bananas</td></tr><tr><td>D4</td><td>=R.Bananas</td></tr><tr><td>C5</td><td>=D.Watermelons</td></tr><tr><td>D5</td><td>=R.Normal</td></tr><tr><td>C6</td><td>=D.Apples</td></tr><tr><td>D6</td><td>=R.Normal</td></tr><tr><td>C7</td><td>=D.Kiwi</td></tr><tr><td>D7</td><td>=R.Kiwi</td></tr><tr><td>C8</td><td>=D.Bricks</td></tr><tr><td>D8</td><td>=R.Bricks</td></tr></tbody></table></td></tr></tbody></table>
Now the next logical step, is to VLookup the Formula's
Name in the above coloured table and match it against the appropriate Type. So, naming the above table FormulaLookup, I would -
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 220px;"><col style="width: 220px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>=Vlookup($A12,FormulaLookup,4,0)</td><td>=Vlookup($A12,FormulaLookup,7,0)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td style="text-align: center;">(Same)</td><td style="text-align: center;">(Same)</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td style="text-align: right;">500</td><td style="text-align: center;">(etc.)</td><td style="text-align: center;">(etc.)</td></tr></tbody></table>
And at the end of the road, my result is this:
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 52px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Amount</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Delivered</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Rotten</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>D.Normal</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Grapefruits</td><td style="text-align: right;">500</td><td>D.Normal</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td>D.Bananas</td><td>R.Bananas</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Oranges</td><td style="text-align: right;">500</td><td>D.Normal</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Apples</td><td style="text-align: right;">500</td><td>D.Apples</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Bananas</td><td style="text-align: right;">500</td><td>D.Bananas</td><td>R.Bananas</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Concrete Bricks</td><td style="text-align: right;">8</td><td>D.Bricks</td><td>R.Bricks</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Kiwi</td><td>
</td><td>D.Kiwi</td><td>R.Kiwi</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td>D.Watermelons</td><td>R.Normal</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td style="background-color: rgb(192, 192, 192); text-align: right;">Watermelons</td><td>
</td><td>D.Watermelons</td><td>R.Normal</td></tr></tbody></table>
...which is exactly
not what I intended.
Now, as it seems that by this point, all of the formula auto-choosing logics actually -work-, and that this design actually makes it possible to add or deduct more such formulas & formula schemes and to change the variables while affecting the should-be-issued formula, all that remains is causing the final stage to actually return the =D.Name value[?] instead of a "D.Name" text string. My current attempts at achieving this included both enclosing the VLookup function within a Value() function; adding "="&Vlookup(etc), both in-and-out of Value() - and so far, all my attempts were unsuccessful.
Any insights will be appreciated.
-- Lorem Ipsum