I'm making a perk calculator. Architecturally I have a sheet with perks, and another sheet where the user can select a set of perks and can see in a "results" cell their total effect.

Examples for perks: "5% armor". "10% damage", "2% armor and if you get hit you become invulnerable for a second". If all of these are selected, the result should be "7% armor, 10% damage, if you get hit you become invulnerable for a second". Naively all perks could be represented as strings, but in that case adding up similar effects (5% armor + 2% armor = 7%) would require a heavy and unmaintainable string-manipulation function.

I've barely used spreadsheets until now, and my understanding is that the natural way to represent this is to have columns for every effect that may need to be summed, so that the cells themselves can contain simple integers.

|perkName |armorBonus|damageBonus|special                |
|perkOne  |    5     |           |                       |
|perkTwo  |          |    10     |                       |
|perkThree|    2     |           | invulnerability blabla|

Because the perks can actually have way more effects than just these two, this creates very sparse data, rows with dozens of columns where only one or two contain data.

Is there an alternative, better way to handle this? Or is this how it is done by people with actual experience?

  • I was also unsure if a design question like this is for programmers, but I decided to post it here, where most of the spreadsheet stuff seems to go. – Fadeway Feb 17 '16 at 19:28
  • This is a good place; it'd be great to have more spreadsheet design questions here. – user79865 Feb 17 '16 at 23:34

You can fold a sparse table into a 3-column table where first column is perk name (repeated for each effect), the second is effect, and the third is the amount (or something).

|   |     A     |    B    |      C       |
| 1 | perkOne   | armor   | 5            |
| 2 | perkTwo   | damage  | 10           |
| 3 | perkTwo   | health  | -1           |
| 4 | perkThree | special | invisibility |
| 5 | perkThree | armor   | 2            |
| 6 | perkThree | stamina | 5            |

This can be easily filtered down to relevant values using filter. For example:

=sum(filter(C1:C, (B1:B="armor")*((A1:A="perkOne")+(A1:A="perkTwo")+(A1:A="perkThree"))))

returns 7, as it filters column C to the values where:

(B = "armor") AND (A = "perkOne" OR A = "perkTwo" OR A = "perkThree")

Naturally, these strings can be references to cells with chosen perks. One can use sum to take OR over an array of perks, etc.

For special (text) values you'll need join rather than sum.

To make column A easier to enter, you can pre-fill it with the formula A2 =A1, dragged down the column. Then every entered perk name is duplicated down, until you replace it with another perk's name.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.