Google-sheets – Is it possible to do sequences in google sheets

google sheets

I'm not sure "sequence" is the right word, but I don't know how else to call it. Here's my problem:

I have a formula to calculate cost of something based on its level. It goes like this:

Cost(i) = 100*2^(2+i)

That works well for one element, but I want to calculate the total cost, of level 1 to i:

TotalCost(4) = Cost(4) + Cost(3) + Cost(2) + Cost(1)

It can be written as a sequence, but I don't know if it's doable in Google Sheet.

Mathematically it speaks more to me to call it a sum (precisely sum_(i=1)^n (100*2^(2+i)), you can see it rendered here, copy and paste it) but what spreadsheets call "sums" is something else, involving multiple cells. All I need is a cell with n, and another one with the formula and the result.

As a sequence, it would be:

U1 = 100*2^(3)
Un = U(n-1) + 100*2^(2+n)

Best Answer

You can make ad-hoc, arbitrary, integer-based ranges using the combination of ARRAYFORMULA and ROW() function. For example, the following formula, inserted anywhere, provides a sum of the first 100 natural numbers:

=sum(arrayformula(row($1:$100)))

You should be able to take it from here, but if not, this is what you seem to need, for i=1 to 100:

=sum(arrayformula(100*2^row($3:$103)))

But it's some absurdly large number so if it's not in fact what you need, please provide a more specific example.

Related Topic