Sometimes I make apps or spreadsheets to help me (or others) make decisions based on specific weighted criteria. It’s hard to find straight-forward, working examples of these kinds of spreadsheets online, so I thought I’d post one.
A few notes:
- There are two Sheets
- Input: You can toggle all of the fields with the white background; the score appears in the right-most column, which is conditionally formatted by score
- Scoring: Here’s where you weight your criteria. If I had wanted to get really fancy with this, I could have categorized the different criteria and weighted them at the category level and apportioned a % of that weight to the various criteria within the category.
- The “Input” sheet has a number of hidden columns where calculations are performed; no need to un-hide them unless you have made changes and want to debug something.
- I strongly recommend calibrating on your methodology by entering a number of sample rows to make sure your weighting on the “Scoring” sheet is producing the scores (in Column P on the “Input” sheet) that you expect.
- For more info about VLOOKUP and other Google Sheets functions, check out the Docs Editors Help Center article.