loop of this formula

[kingman29]

Hello
I have a Monster function,
is that possible to make this function inVBA ?
A1 = 15%
B1 = +POWER(A1;1)-POWER(A1;2)-POWER(A1;3)-POWER(A1;4)-POWER(A1;5)
witch mean every time we subtract A1 with the increase of power

So My Vba code is :

Sub Calculate()
Range("B1") = Range("A1") ^ 1 - Range("A1") ^ 2 - Range("A1") ^ 3 - Range("A1") ^ 4 - Range("A1") ^ 5 - Range("A1") ^ 6

End Sub

Is that anyway to make this Code more professional ?
what If I have Power of 56 times ?
Thank you *
note : My windows and Office in French Language

 

[GWteB]

The difference in calculation results is caused by the way Excel and VBA handle Doubles.
If the head line of @JackDanIce's UDF had been written like

Public Function myFunction(my_input As Range, my_power As Long) As Double

instead of

Public Function myFunction(my_input As Double, my_power As Long) As Double

the outcome of the UDF and the original code of @kingman29 would be the same.

 

[JackDanIce]

Thanks @GWteB I read about this afterwards, that Excel converts to double, regardless of cell value so passing in a range as argument makes more sense. I think I was trying to be cute/clever to leave out that conversion step or at least the process from sheet level into calc and output at sheet level.

I'm guessing it's some data type/conversion and then raised to a power, with the number of DPs needed for accuracy (2DP is almost air tight but lets not go there!)

Appreciate the explanation, I was curious

 

[GWteB]

@JackDanIce, I owe you an apology since I was terribly wrong

I should have looked into this further but didn't. The moment I would post my contribution I saw there were already enough possible solutions, so I didn't post it. When I read the OP had discovered a difference in the end result, while the end result of my solution (also a UDF) matched his, I noticed the difference in argument declaration. My premature conclusion was that this had to be the cause (indeed on account of type conversion), but nothing could be further from the truth!!

If I put your post #2 code and my code together (and use the same variable names for better comparison) I think I don't need to explain anything further. Cheers.

Public Function MyFunction(my_input As Double, my_power As Long) As Double
    
    ' JackDanIce
    Dim x As Long
    MyFunction = my_input
    For x = 2 To my_power
        MyFunction = MyFunction - MyFunction ^ x
    Next x
End Function


Public Function MyFunction(my_input As Range, my_power As Range) As Double
    
    ' GWteB
    Dim x As Long
    MyFunction = my_input.Value
    For x = 2 To my_power.Value
        MyFunction = MyFunction - my_input.Value ^ x
    Next x
End Function

 

[Eric W]

There is a formula to sum up a geometric series, which can be adapted for this.

Book1
	A	B	C	D
1	0.15		0.15	5
2	0.123543		0.123543
Sheet16

Cell Formulas
Range	Formula
A2	A2	=POWER(A1,1)-POWER(A1,2)-POWER(A1,3)-POWER(A1,4)-POWER(A1,5)
C2	C2	=C1-((C1^2)*(1-C1^(D1-1)))/(1-C1)

No loop or even UDF needed.

 

[johnnyL]

Is that anyway to make this Code more professional ?
what If I have Power of 56 times ?

@Eric W How would those cell formulas look? LOL

 

[Eric W]

@Eric W How would those cell formulas look? LOL

The C2 formula IS the final formula. If you want to extend the formula 56 times, change the 5 in D1 to 56.

 

[johnnyL]

Ahaaa! So the Column A data is the old version and the Column C Data is your submission. Thanks for confusing me. LOL

Genius as always @Eric W !

 

[GWteB]

Not all Excel users are math wizards
Nice formula!

 

[Eric W]

Sorry about that! I should have been more clear. It also occurred to me that Excel probably has a function for that, and it does. So here are the updated versions:

Book1
	A	B	C	D
1	0.15		0.15	5
2	0.123543	<--Original version	0.123543	<--Coding the formula directly
3			0.123543	<-- Using the built-in function
4			0.123543	<-- Excel 365 version
Sheet16

Cell Formulas
Range	Formula
A2	A2	=POWER(A1,1)-POWER(A1,2)-POWER(A1,3)-POWER(A1,4)-POWER(A1,5)
C2	C2	=C1-((C1^2)*(1-C1^(D1-1)))/(1-C1)
C3	C3	=A1-SERIESSUM(A1,2,1,ROW(INDIRECT("1:"&D1-1))^0)
C4	C4	=A1-SERIESSUM(A1,2,1,SEQUENCE(D1-1,,,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

(Yes, I'm definitely a big math geek!)

 

[GWteB]

(Yes, I'm definitely a big math geek!)

I'm not, yet I like geeks in a certain way ?