summation (sigma) in Excel

Question

How can I get

Ʃ 2^n 

in via a formula in a cell in Excel, for n from 1 to N when N is a value in a neighbouring cell? I had a look at some related questions here, tried around and ended up with this here, which does not work (generates #NAME?). Can anybody correct this and explain?

=SUMPRODUCT(POW(2,ROW(INDIRECT(CONCATENATE("1:",L41)))))

EDIT: omg, it's the POW which doesn't exist in Excel, should be POWER, how embarrassing. I always stumble across this, coming from C/C++. I thought the problem has to be somewhere in the other functions I never used before. Thanks anyways. I keep the question online though, maybe it is helpful to somebody.

Answer 1

I still would like to remark that Ʃ 2^n for 1 upto N is 2^(N+1) - 2

=2^(L41+1)-2
=POWER(2, L41+1)-2

That is the POWER function or the ^ operator will do nicely.

BITLSHIFT for 2^N might not work always (floating point, Excel 2010).

Answer 2

Perhaps:

=SUMPRODUCT(2^ROW(INDEX(A:A,1):INDEX(A:A,A1)))

where A1 holds N

See:

Previous Answer