Hi,
I want the macro that can perform the following task.
k = 5 + 6 + 7 + ... + 14 + 15 (=110)
the value of cell A1 is k
Could you please write me the macro?
I want to learn.
Thanks
Hi,
I want the macro that can perform the following task.
k = 5 + 6 + 7 + ... + 14 + 15 (=110)
the value of cell A1 is k
Could you please write me the macro?
I want to learn.
Thanks
We don't understand what it is you want?, you don't need a macro for that simply use a worksheet function.
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
There are four variables for that calculation. High, Low, Count, and Interval.
In that example High = 15, Low = 5, Count = 11, Interval = 1
k = (High + Low) * Count / 2
also
k = (High + Low) * (1+(ABS(High-Low) / Interval )) / 2
Consider 5 + 10 + 15 = 30
To put it in terms of your worksheet ranges use it like this
=(MAX(B1:B11) + MIN(B1:B11)) * (1+(ABS(MAX(B1:B11)-MIN(B1:B11)) / 1)) / 2
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
If they are in worksheet ranges, wouldn't SUM(B1:B11) work?
Hmm. The OP hasn't clarified what the goal is, perhaps they want
Given K, find an arithmetic series that sums to K. Hmm..
Certainly not the most flexible way, but it seems to be what you asked for.
[VBA]
Option Explicit
Sub MakeA1_110()
ActiveSheet.Range("a1").Value = 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 + 14 + 15
End Sub
[/VBA]
Paul
thank you for your reply:Originally Posted by Paul_Hossler
In fact, I used a mathematical formula.
[VBA]
Sub sum_from_5_to_15()
n = 11
a = 5
d = 1
i = (n / 2) * (2 * a + (n - 1) * d)
Cells(1, 1).Value = i
End Sub
[/VBA]
I just want to know whether there is an Excel vba code that can perform 5 + 6 + .... +14 + 15 .
You just wrote one.
You could cast it as a function.
[vba]Function ArithmeticSum(a as Double, d as Double, n as Double) As Double
Rem a is the start number, d the interval, n the number of terms
ArithmeticSum = (n / 2) * (2 * a + (n - 1) * d)
End Function[/vba]