Reverse calculation from doubling a number

[johncassell]

Totally useless title and I apologise for that!

Hoping someone can help with this please?

If I start with 1 and keep doubling 1, 2 , 4, 8, 16

The total of all of those numbers is 31

Is there an excel formula available for me to say, my start number is 1 and my end number is 16, tell me what the total is - and the formula would calculate 31.

Thanks
John

[Bob Phillips]

Try this

=IF(MOD(LOG(endnum,2),1)=0,((2*startnum)^(LOG(endnum,2)+1))-1,NA())

[ianh]

Try this

=IF(MOD(LOG(endnum,2),1)=0,((2*startnum)^(LOG(endnum,2)+1))-1,NA())

That excellent bob

[ianh]

Example.xlsx

Have a look at my example attached
Put the numbers in the spaces next to start and end and the results should be correct in the total.

Its a bit of a hashed work around and im sure there will be better solutions

[Bob Phillips]

Attachment 136009

Have a look at my example attached
Put the numbers in the spaces next to start and end and the results should be correct in the total.

Its a bit of a hashed work around and im sure there will be better solutions

Shouldn't the formula in B8 be

=MATCH(B6,B2:U2,1)+1

[Bob Phillips]

In fact you might as well do it all in one formula in B7

=SUM(A2:INDIRECT(ADDRESS(2,MATCH(B6,B2:U2,1)+1,3,1,)))

[ianh]

Shouldn't the formula in B8 be

=MATCH(B6,B2:U2,1)+1

Yes, it should.

I'd left the intermittent steps in to show how id got to the end product but yes it could be done in one.

The advantage of this way would be if you didn't know the final "number" in the sequence. it will giv you the nearest matching value.
You could probably add a cell to then tell you the remainder/correct highest possible value in the sequence.

[Bob Phillips]

Yes, it should.

I'd left the intermittent steps in to show how id got to the end product but yes it could be done in one.

The advantage of this way would be if you didn't know the final "number" in the sequence. it will giv you the nearest matching value.
You could probably add a cell to then tell you the remainder/correct highest possible value in the sequence.

I took a different approach, the final number has to be in the desired sequence .

[ianh]

your solution in much more elegant bob, and probably what the OP is asking for.
I'm learning as i go with excel and try to have a stab at the problems on here to help me learn.

[MrShorty]

A couple of different ideas:

1) Use the SERIESSUM(x,n,m,a) function. x=2, n=0, m=1, and a is an array of 1's as large as you need (a might be the hardest parameter to piece together). So =SERIESSUM(2,0,1,{1,1,1,1,1}) returns 31

2) Because each term is going to be a power of 2, the problem could be expressed as a conversion from a binary number to a decimal number, which has its own function in Excel. =BIN2DEC(11111) returns 31.

[ianh]

and today i have learnt three new things.
never used seriessum before. Can you talk me through it in a little more detail MrShorty, if you have time.

[johncassell]

Hi Bob, exactly what I was looking for thanks very much.

Thanks for the other replies as well but the first reply did what I needed.

Cheers!

John

[Bob Phillips]

A couple of different ideas:

1) Use the SERIESSUM(x,n,m,a) function. x=2, n=0, m=1, and a is an array of 1's as large as you need (a might be the hardest parameter to piece together). So =SERIESSUM(2,0,1,{1,1,1,1,1}) returns 31

2) Because each term is going to be a power of 2, the problem could be expressed as a conversion from a binary number to a decimal number, which has its own function in Excel. =BIN2DEC(11111) returns 31.

Nice ideas.

As you say, it needs to cater for variable end numbers

=SERIESSUM(2,0,1,ROW(INDIRECT("1:"&LOG(endnum,2)+1))^0)

and

=BIN2DEC(LEFT(REPT(1,99),LOG(endnum,2)+1))

and it should also cater for a non valid end number.

[MrShorty]

never used seriessum before. Can you talk me through it in a little more detail MrShorty, if you have time.

I'm sure I could, but I don't use it that much myself, so everything I could say about it would pretty much be straight out of its help file. Basically, I use it mostly as a formula for building generic polynomials.

I know the OP said he was satisfied with the first answer, but it was still an intriguing question. I just had a thought. Can't we show (maybe by induction) that sum(2^i) (for i=0 to n) = 2^(n+1) - 1? I can show this is true for n=0, and the example he uses (n=4) fits. (sum(2^0...2^4)=31=2^5-1 is true.)

Since it has been a long time since I've tried a proof by induction, I gave it a shot, and I think I succeeded in proving this to be true. If we can adequately convince ourselves, then a formula we can use will be =2^(endpower+1)-1 if we want to give it n, or =2^(log(endnum,2)+1)-1 if we want the formula to figure n from the last number in the series.