Ok. Attached is a sanitized, simplified sheet.
The "BorrowingRate" sheet shows the problem. Only cell K4 should have an NA error, because we are missing some data for that month. The other cells should all have a value, which comes from taking calculated values from the "CPI" sheet, and looking up values on the "CPIRate" sheet (I use some named formulas, btw).
So the "NY" value on the first row should have a result: 71% to be exact. Below this table, I added some extra stuff to make comparison easier. The lookup vs calculated rows at the bottom have the same formulas - they just point to a different variable, located in the "Calculated" and "Hard-Coded" rows above.
Let me know if this example is not clear.
Thanks.
Bookmarks