mh-z web-log

For curiosity’s sake, I bought a bottle of C2 (that new flavor of Coca Cola with half the calories of regular Coke) and this is my initial reaction after swilling several sips: It tastes just like chicken. Chicken which, in turn, tastes like a proportional combination of Classic Coke and Diet Coke. Perhaps more like 60/40 with the emphasis on the non-Nutrasweet bend. I thought the mad (not angry) scientists in Coca Cola’s secret underground lavatories would have flushed out something a bit different, a bit bolder, but no, this is something John Doe could concoct in his own kitchen by combining regular and Diet Cokes at the desired ratio. He could even bravely throw in a few parts A&W Cream Soda to affect the bold splash those mad (not angry) scientists overlooked. Anyway, it’s not bad. And on further reflection I’d say, more like 75/25.

It’s a strange relationship, not being circular, as one would expect. "Without oaks there could be no mice, without mice no cats, and without cats no oaks." Oaks feed mice, mice feed cats, but cats don’t feed oaks. So without cats and mice, there still could be oaks. But if you had just oaks and cats, that would be fine (for the oaks) although the cats could not exist because they would have nothing to eat. If you had oaks and mice, the oaks would be okay until they were gnawed to death by the overabundant mice, which (having taken over the world) would subsequently die, so that scenario wouldn’t happen. There would be a predator-prey relationship between the mice and the oaks, like there is between sheep and wolves (the classic example). Both would coexist fine, with a balance in both populations being struck. Now, if you had just cats and mice, the mice would die out first (of starvation), followed by the cats. With all three entities (oaks, cats, and mice) in existence, a reduction in any one of them would hurt one of the other two, but the only combinations under which the entities could not coexist is oaks and cats, and cats and mice. Here are all the combinations: Oaks alone: Would thrive. Cats alone: Wouldn’t have anything to eat; would die out. Mice alone: Wouldn’t have anything to eat; would die out. Oaks and cats: Cats would die; oaks would thrive. Cats and mice: All would die (mice would starve to death; followed by cats). Mice and oaks: Predator-prey; both would coexist. Oaks, cats and mice: It’s an ecosystem! Now I’m tempted to make a diagram. Of all the useless things to do… Well, I think I’ll go play Minesweeper instead. No, I hate that game (because you often get into situations where logic alone won’t help you, so you just have to guess).

Built-in function to simulate custom functions using worksheets only.

1. Decide on a set of cells to be your inputs, and one cell to be your output. (Ex: let cells A1,B1 and C1 be the inputs, and let A2 be the output.)
2. Call the new function something like PLUGIN or CALC or APPLY or something short like F.
3. To run our example function on the values 7,8 and 9, use =F(A1,7,B1,8,C1,9,A2).
4. The result of the “function call” acts as if the actual numbers 7,8,9 were typed into cells A1,B1,C1 (respectively), and the result was read out of A2 and inserted into the current cell (that contains the “=F(…)” formula).

• The “function definition” can include many intermediate cells used in the calculation of the result cell.
• The “function” has cells chosen by convention for its inputs and outputs.
• The “function call” doesn’t actually change anything; it only runs as if the inputs/output of the “function” were changed.

Example: Function to change error values to Null; uses A1 as input, and itself as output. =IF,Null,A1) If the above “function” were put in B1, we could use “=F(A1,your_formula_here,B1)” to return Null if the formula “your_formula_here” returned an error. For one-step functions like this one, the old way to calculate this would have been: =IF,Null,your_formula_here) That’s wasteful because “your_formula_here” must be duplicated. In a long formula where the result could be an error code, you could store the result in one cell and clean it up in another cell, but that clutters up the spreadsheet.

This “wish” can’t (easily) be simulated using VBA, since in writing your own worksheet function the code can’t change the contents of other cells. You’d have to copy the sheet with the “function” definition into memory and get Excel to do calculations in the temporary memory copy.