Great question! I am using column headers as context signals. If a column is named 'Rate', 'Price', 'Percentage', or 'Count', I'm more lenient with constants in formulas referencing it.
For ambiguous cases like 'Total', I currently flag it and let the user decide—which isn't ideal. I've been considering a confidence score system where:
High confidence whitelist: 24, 60, 7, 365 (time conversions)
Context-dependent: numbers near column headers with semantic meaning
Always flag: arbitrary numbers like 1.2, 847, etc. unless they're in a 'Constants' or 'Assumptions' section
The hardest edge case is something like Revenue * 0.15 where 0.15 might be a legitimate tax rate OR a hardcoded assumption that should be in a named cell. Right now I flag it as medium priority.
How would you approach this?
Spreadsheets are the only programming language where approximate string matching is the default behavior (VLOOKUP with range_lookup=TRUE). I can't think of another language where fuzzy matching happens silently unless you explicitly opt out. Is this the most dangerous design decision in computing history?
High confidence whitelist: 24, 60, 7, 365 (time conversions) Context-dependent: numbers near column headers with semantic meaning Always flag: arbitrary numbers like 1.2, 847, etc. unless they're in a 'Constants' or 'Assumptions' section
The hardest edge case is something like Revenue * 0.15 where 0.15 might be a legitimate tax rate OR a hardcoded assumption that should be in a named cell. Right now I flag it as medium priority. How would you approach this?