Sum of value in cell range between 500,000 to 1,000,000
 

Dear Friends I need a formula to find numberor a sum of value ranges bewtween
500,000 to 1,000,000

Sum of value in cell range between 500,000 to 1,000,000
 

Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Khawajaanwar" wrote in message
...
Dear Friends I need a formula to find numberor a sum of value ranges
bewtween
500,000 to 1,000,000

Sum of value in cell range between 500,000 to 1,000,000
 

"Rob van Gelder" wrote...
Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub
....

Brute force. Better to use Gauss's formula.

MsgBox 1000000 * 1000001 / 2 - 499999 * 500000 / 2

which recognizes that

Sum(500000..1000000) = Sum(1..1000000) - Sum(1..499999)

Amazing what a little math does for programming.

Sum of value in cell range between 500,000 to 1,000,000
 

I must admit I wasn't aware of Gauss's formula though suspected there must
be a quicker way - so thanks.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harlan Grove" wrote in message
...
"Rob van Gelder" wrote...
Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub
...

Brute force. Better to use Gauss's formula.

MsgBox 1000000 * 1000001 / 2 - 499999 * 500000 / 2

which recognizes that

Sum(500000..1000000) = Sum(1..1000000) - Sum(1..499999)

Amazing what a little math does for programming.

Sum of value in cell range between 500,000 to 1,000,000
 

Try the following:

Dim Rng As Range
Dim Total As Double
For Each Rng In Range("A1:A10") '<<< CHANGE range
If Rng.Value = 500000 And Rng.Value <= 1000000 Then
Total = Total + Rng.Value
End If
Next Rng



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Khawajaanwar" wrote in
message
...
Dear Friends I need a formula to find numberor a sum of value
ranges bewtween
500,000 to 1,000,000

Sum of value in cell range between 500,000 to 1,000,000
 

Dear Friends thank you for showing the interest, As I am new in this field
please also help me where can I wrote these formulas.
Thanks once again
Khawajaanwar

"Rob van Gelder" wrote:

Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Khawajaanwar" wrote in message
...
Dear Friends I need a formula to find numberor a sum of value ranges
bewtween
500,000 to 1,000,000

Sum of value in cell range between 500,000 to 1,000,000
 

You can do this with worksheet formula, using the method that Gauss
(allegedly) devised as a schoolboy. This solution assumes that the first
number goes in A1, the second in A2, and allows for starting values that are
odd or even, and the last number being odd or even (this affects the
solution, because the basic method assumes an even number of entries

=IF(OR(AND(MOD(A1,2)0,MOD(A2,2)=0),AND(MOD(A1,2)= 0,MOD(A2,2)0)),(A2+A1)*IN
T((A2-A1+1)/2),A1+(A1+1+A2)*INT((A2-(A1+1)+1)/2))

or a bit simpler

=IF(MOD(A2-A1,2),(A2+A1)*INT((A2-A1+1)/2),A1+(A1+1+A2)*INT((A2-(A1+1)+1)/2))

--
HTH

-------

Bob Phillips
"Khawajaanwar" wrote in message
...
Dear Friends thank you for showing the interest, As I am new in this field
please also help me where can I wrote these formulas.
Thanks once again
Khawajaanwar

"Rob van Gelder" wrote:

Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Khawajaanwar" wrote in message
...
Dear Friends I need a formula to find numberor a sum of value ranges
bewtween
500,000 to 1,000,000