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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.