PDA

View Full Version : k = 5 + 6 + 7 + ... + 14 + 15



clarksonneo
08-20-2011, 10:14 PM
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
08-20-2011, 11:06 PM
We don't understand what it is you want?, you don't need a macro for that simply use a worksheet function.

mikerickson
08-20-2011, 11:25 PM
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
08-20-2011, 11:32 PM
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
08-21-2011, 12:53 AM
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
08-22-2011, 07:54 AM
Certainly not the most flexible way, but it seems to be what you asked for.



Option Explicit
Sub MakeA1_110()
ActiveSheet.Range("a1").Value = 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 + 14 + 15
End Sub


Paul

clarksonneo
08-22-2011, 09:20 AM
Certainly not the most flexible way, but it seems to be what you asked for.



Option Explicit
Sub MakeA1_110()
ActiveSheet.Range("a1").Value = 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 + 14 + 15
End Sub

Paul

thank you for your reply:

In fact, I used a mathematical formula.


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



I just want to know whether there is an Excel vba code that can perform 5 + 6 + .... +14 + 15 .

mikerickson
08-22-2011, 12:05 PM
You just wrote one.
You could cast it as a function.

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