Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.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. |
#5
Posted to microsoft.public.excel.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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming |