ARRAYFORMULA with SUM and CHOOSE

Question

I have a set of values, for exemple :

14 ; 17 ; 12 ; 14 ; 12 ; 8

You might recognize a set of DnD ability scores. I want to calculate the amount of creation point used for these.

So I would like to combine SUM and CHOOSE. I tried this (same as in my working LibreOffice sheet) :

=ARRAYFORMULA(SUM(CHOOSE(C9:C14 - 6; -4; -2; -1; 0; 1; 2; 3; 5; 7; 10; 13; 17)))

I expected 25 (5+13+2+5+2-2), got 5. For some reason it stops right after the first value without doing any sum.

AdamL · Accepted Answer · 2016-11-04 09:51:55Z

1

To be clear, +Robin Gertenbach's answer is much more intuitive than this, but this is a bit shorter:

=ArrayFormula(SUM(HLOOKUP(-4,{-4;-2;-1;0;1;2;3;5;7;10;13;17},C9:C14-6)))

answered Nov 4, 2016 at 9:51

AdamL

24.8k7 gold badges73 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AdamL Over a year ago

The first argument of the HLOOKUP must be whatever the first element in the lookup array is - in this case -4. So this value will always be "found" at the top of the one-column array. The "variable" is represented in the third argument, which is stipulating which row number to return.

Liios Over a year ago

I see, clever. I think I will be using this then. Tank you a lot.

Robin Gertenbach · Accepted Answer · 2016-11-04 13:30:01Z

1

Some functions unfortunately don't work with ArrayFormulae, CHOOSE seems to be one of them.

Unfortunately Index doesn't either, VLookup does though so we have to create this monstrosity:

=ArrayFormula(SUM(VLOOKUP(
  $C$9:$C$14 - 6, 
  {1, -4;
   2, -2;
   3, -1;
   4, 0;
   5, 1;
   6, 2;
   7, 3;
   8, 5;
   9, 7;
   10, 10;
   11, 13;
   12, 17}, 2)))

If you have room somewhere to create a table that can be referenced the formula will be much simpler of course.

Another approach would be a custom function:

function abilityScoreCost(scores) {
  var costs = {
    7:  -4,
    8:  -2,
    9:  -1,
    10: 0,
    11: 1,
    12: 2,
    13: 3,
    14: 5,
    15: 7,
    16: 10,
    17: 13,
    18: 17
  }
  return scores
      .map(function (score) {
        return costs[score];
      }).reduce(function (x,y) {
        return x + y;
      });
}

that you could then call with =abilityScoreCost(C9:C14)

edited Nov 4, 2016 at 13:30

answered Nov 4, 2016 at 8:07

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

2 Comments

Liios Over a year ago

This is a working solution indeed but I would rather avoid disseminate my data everywhere. Thank you tho, this will be my fallback.

Robin Gertenbach Over a year ago

How about a GAS function? Not much more elegant but a simpler formula.

Collectives™ on Stack Overflow

ARRAYFORMULA with SUM and CHOOSE

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related