k = 5 + 6 + 7 + ... + 14 + 15

[clarksonneo]

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

[Simon Lloyd]

We don't understand what it is you want?, you don't need a macro for that simply use a worksheet function.

[mikerickson]

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

[Simon Lloyd]

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

[mikerickson]

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..

[Paul_Hossler]

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

[clarksonneo]

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:

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 .

[mikerickson]

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]