I do not think mikekaganski’s solution is the answer.
Big picture, this is a financial planning spreadsheet. Just one smaller component of the spreadsheet is calculating future costs for “reserve” items. Reserve items are high cost events that do not occur yearly, for example replacing a hot water heater. There may be rows for a hundred of different reserve items with column for a span of 100 years. To calculate the expense for any item in the list, one needs to know what is the FINAL year it is subject to being an expense, what was the PREV year it was replaced, what is the YEAR the calculation is being done for, what is the expected LIFE of the item, and what was the PRICE of the item at what YEAR. There is also a constant, INFLATION that is common to all items. The equation to calculate the amount for any item for any year is this.
((YEAR > FINAL) || (YEAR < PREV)) ? 0 :
((YEAR - PREV - EXTRA) % LIFE) != 0 ? 0 :
COST * (INFLATION ^ (YEAR - PRICED))
This is a formula implementing that using a macro function (SRC is a named range that passes in an argment that indicates the calling cells row and col number).
IF OR(GETVAL_(SRC,"YEAR") > GETVAL_(SRC,"FINAL"), GETVAL_(SRC,"YEAR") < GETVAL_(SRC,"PREV"),
0,
IF MOD(GETVAL_(SRC,"YEAR") - GETVAL_(SRC,"PREV") - GETVAL_(SRC,"EXTRA"), GETVAL_(SRC,"LIFE")) > 0,
0
GETVAL_(SRC,"COST") * (INFLATION ^ (GETVAL_(SRC,"YEAR")-GETVAL_(SRC,"PRICED"))),
)
)
Without a function, unless I am going to hardcode cell addresses (ugh), the only way I have found to do this is with INDIRECT and ADDRESS and you get this even uglier mess.
IF OR(INDIRECT(YEAR,COLUMN(),1)) > INDIRECT(ADDRESS(ROW(),FINAL,1)),INDIRECT(ADDRESS(YEAR,COLUMN(),1)) < INDIRECT(ADDRESS(ROW(),PREV,1)),
0,
IF MOD(INDIRECT(ADDRESS(YEAR,COLUMN(),1)) - INDIRECT(ADDRESS(ROW(),PREV,1)) - INDIRECT(ADDRESS(ROW(),EXTRA,1)), INDIRECT(ADDRESS(ROW(),LIFE,1))) > 0,
0
INDIRECT(ADDRESS(ROW(),COST,1)) * (INFLATION ^ (INDIRECT(ADDRESS(YEAR,COLUMN(),1))-INDIRECT(ADDRESS(ROW(),PRICED,1)))),
)
)
To quote Knuth,
Instead of imagining that our main task is to instruct a computer what to to, let us concentrate rather on explaining to human beings what we want a computer to do.
Most of the 421 characters in the forumla above are noise that obscures the meaning of an already complex equation. Recall this is just one example of a situation that occurs in other places in the spread sheet. And in this one, there are only a few variables to deal with. Because functions can be passed parameters and named rnages cannot, I would need hundreds of named ranges to deal with the entire problem and maintaining many named ranges is a nightmare (there is no way of exporting or importing them so they can be easily be processed outside the spreadsheet). And that is why we have functions, so we do not have to write down something many times when all that is different is a parameter.
As to using VLOOKUP or MATCH, I hard coded the column/row numbers into this particular function because if there are changes, the only the function needs to changed and there only a few variables. In other places where there are many more variables and the columns are more likely to change, I do use VLOOKUP or MATCH. But that added robustness makes the formula even more complex by adding even more noise. Instead of this,
INDIRECT(ADDRESS(ROW(), COST,1))
You get this (HeaderRow is a named range for something like $A$2:$ZZ$2)
INDIRECT(ADDRESS(ROW(), MATCH("COST",HeaderRow,0), 1))
If anyone is interested, the macro function can be seen here, https://w3.cs.jmu.edu/arch/tmp/macro.txt but I will only keep it there temporarily. If anyone is reading this months down the line, it may no longer be available.
