Sumproduct troubleshooting (new to me)

M

Matt S

Hello,

I am trying to create a table that counts the number of instances my
"lambda" value and my "temperature" value fall within a range. My lambda
values are in column P and my temp values in N. I am creating a sheet as
such, where I am trying to count the number of times lambda and temp fall
within the range. I.E. the first count would be the number of instances
lambda falls between 2 and 2.005.

Lambda Count Lambda Temp Count Temp
2 20
2.005 x 21 x
2.01 x 22 x
2.015 x 23 x


This is my code so far... please help! Please look to see why this is
giving me such a hard time. The error is coming in on the "LambaRange" and
"TempRange" within my SumProduct function. I tried replacing them with
P2:p1000, but then it highlighted the ":" and said ")" expected.



'Make Lambda and Temp Distribution Plots

Dim LambdaRange As Range
Dim TempRange As Range

Sheets("Runlog").Select

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ddist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 2
Range("AG8").Value = 20

'Define Ranges within runlog
Set LambdaRange = Range(Cells(1, "P"), Cells(LastRow, "P"))
Set TempRange = Range(Cells(1, "N"), Cells(LastRow, "N"))


'create temp and lambda table and count # of instances data fell between
ranges
For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).Value = SumProduct((LambdaRange > _
Range("AF" & i - 1).Value) * _
(LambdaRange < Range("AF" & i).Value))
Next

For i = 9 To NumTRows
Range("AH" & i).Value = Range("AH" & i - 1).Value + TDist
Range("AI" & i).Value = SumProduct((TempRange >= _
Range("AH" & i - 1).Value) * _
(TempRange <= Range("AH" & i).Value))
Next
 
D

Dave Peterson

VBA doesn't have a Sumproduct function.

Untested...

Range("AG" & i).Value = activesheet.evaluate("SumProduct((" _
& LambdaRange.address & ">" _
& Range("AF" & i - 1).Value & ") *" _
& (" & LambdaRange.address & "<" & Range("AF" & i).Value _
& "))")

Another option (which may be easier) might be to plop the formula into the cell,
then convert to values.

with Range("AG" & i)
.formula = "=SumProduct((" _
& LambdaRange.address & ">" _
& Range("AF" & i - 1).Value & ") *" _
& (" & LambdaRange.address & "<" & Range("AF" & i).Value _
& "))"
.value = .value
end with

(also untested)
 
D

Don Guillett

Try this idea where sumproduct requires evaluate

'=SUMPRODUCT(--(A1:A21="apple"),G1:G21)
Range("i2").Value = Evaluate("SUMPRODUCT(--(A1:A21=""Apple""),G1:G21)")
 
M

Matt S

It seems to have issues with the quotations on the Range("AF" * i-1) portion,
so I replaced it with the relative reference. It now gives me an application
or object defined error. Any ideas? Thanks again for your help!

Here's what it looks like now:


'create temp and lambda table and count # of instances data fell between
ranges
For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist

With Range("AG" & i)
.Formula = "=SumProduct((" & LambdaRange.Address & ">
R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"
.Value = .Value
End With
Next
 
M

Matt S

Dave,

Thanks for your help! I played around with it and finally got it to work!
I'd actually like to keep the function in the worksheet, so I removed the
..value portion. Here's what I came up with below. Seems the difference
between what I did and what you did was that I did not put the .value portion
on the range("AF" & i). Not exactly sure why that would be the deciding
factor.

Thanks again!
Matt


'Make Lambda and Temp Distribution Plots

Dim LambdaRange As Range
Dim TempRange As Range

Sheets("Runlog").Select

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.8
Range("AH8").Value = 20

'Define Ranges within runlog
Set LambdaRange = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))


'create temp and lambda table and count # of instances data fell between
ranges
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).Formula = "=SUMPRODUCT((" & LambdaRange.Address & ">=
" & Range("AF" & i - 1) & ") * (" & LambdaRange.Address & "<=" & Range("AF" &
i) & "))"
Next

For i = 9 To NumTRows
Range("AH" & i).Value = Range("AH" & i - 1).Value + TDist
Range("AI" & i).Formula = "=SUMPRODUCT((" & TempRange.Address & ">= "
& Range("AH" & i - 1) & ") * (" & TempRange.Address & "<=" & Range("AH" & i)
& "))"
Next


Not sure what was dif
 
D

Don Guillett

'=SUMPRODUCT(--(A1:A21="apple"),G1:G21)
Range("i2").Value = Evaluate("SUMPRODUCT(--(A1:A21=""Apple""),G1:G21)")
to place formula
Range("i3").Formula = "=SUMPRODUCT(--(A1:A21=""apple""),G1:G21)"
to convert formula to value
Range("i3").value=Range("i3").value

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Matt S said:
It seems to have issues with the quotations on the Range("AF" * i-1)
portion,
so I replaced it with the relative reference. It now gives me an
application
or object defined error. Any ideas? Thanks again for your help!

Here's what it looks like now:


'create temp and lambda table and count # of instances data fell
between
ranges
For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist

With Range("AG" & i)
.Formula = "=SumProduct((" & LambdaRange.Address & ">
R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"
.Value = .Value
End With
Next




Dave Peterson said:
VBA doesn't have a Sumproduct function.

Untested...

Range("AG" & i).Value = activesheet.evaluate("SumProduct((" _
& LambdaRange.address & ">" _
& Range("AF" & i - 1).Value & ") *" _
& (" & LambdaRange.address & "<" & Range("AF" & i).Value
_
& "))")

Another option (which may be easier) might be to plop the formula into
the cell,
then convert to values.

with Range("AG" & i)
.formula = "=SumProduct((" _
& LambdaRange.address & ">" _
& Range("AF" & i - 1).Value & ") *" _
& (" & LambdaRange.address & "<" & Range("AF" & i).Value
_
& "))"
.value = .value
end with

(also untested)
Click to expand...
Click to expand...
 
D

Dave Peterson

Glad you got it working!

Just for completeness...

.Formula = "=SumProduct((" & LambdaRange.Address _
& "> R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"

is a mixture of R1C1 reference style (r[-1]c[-1] and rc[-1] and A1 reference
style (in both .address portions)--and you used .formula instead of
..formulaR1C1.

..FormulaR1C1 = "=SumProduct((" & LambdaRange.Address(referencestyle:=xlR1C1) _
& "> R[-1]C[-1]) * (" _
& LambdaRange.Address(referencestyle:=xlR1C1) & "< RC[-1])))"

Still untested!
 
M

Matt S

With the FormulaR1C1 function, the
LambdaRange.Address(referencestyle:=xlR1C1) gives a range that is not in the
form $P$8:$P$64008. It's in the form P8:p64008, so when I extend the formula
down, it is a floating range. Any way to lock it in place?

Thanks,
Matt



Dave Peterson said:
Glad you got it working!

Just for completeness...

.Formula = "=SumProduct((" & LambdaRange.Address _
& "> R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"

is a mixture of R1C1 reference style (r[-1]c[-1] and rc[-1] and A1 reference
style (in both .address portions)--and you used .formula instead of
..formulaR1C1.

..FormulaR1C1 = "=SumProduct((" & LambdaRange.Address(referencestyle:=xlR1C1) _
& "> R[-1]C[-1]) * (" _
& LambdaRange.Address(referencestyle:=xlR1C1) & "< RC[-1])))"

Still untested!

Matt said:
Dave,

Thanks for your help! I played around with it and finally got it to work!
I'd actually like to keep the function in the worksheet, so I removed the
.value portion. Here's what I came up with below. Seems the difference
between what I did and what you did was that I did not put the .value portion
on the range("AF" & i). Not exactly sure why that would be the deciding
factor.

Thanks again!
Matt

'Make Lambda and Temp Distribution Plots

Dim LambdaRange As Range
Dim TempRange As Range

Sheets("Runlog").Select

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.8
Range("AH8").Value = 20

'Define Ranges within runlog
Set LambdaRange = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))


'create temp and lambda table and count # of instances data fell between
ranges
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).Formula = "=SUMPRODUCT((" & LambdaRange.Address & ">=
" & Range("AF" & i - 1) & ") * (" & LambdaRange.Address & "<=" & Range("AF" &
i) & "))"
Next

For i = 9 To NumTRows
Range("AH" & i).Value = Range("AH" & i - 1).Value + TDist
Range("AI" & i).Formula = "=SUMPRODUCT((" & TempRange.Address & ">= "
& Range("AH" & i - 1) & ") * (" & TempRange.Address & "<=" & Range("AH" & i)
& "))"
Next

Not sure what was dif
Click to expand...
Click to expand...
 
D

Dave Peterson

Take a look at .address in VBA's help.

You'll see that you can specify that, too:

RowAbsolute Optional Variant. True to return the row part of the reference as
an absolute reference. The default value is True.

ColumnAbsolute Optional Variant. True to return the column part of the
reference as an absolute reference. The default value is True.

LambdaRange.Address(rowabsolute:=false, columnabsolute:=false, )
referencestyle:=xlR1C1)

Or using them as positional parameters:

LambdaRange.Address(false, false, xlR1C1)
and you may see it as:
LambdaRange.Address(0, 0, xlR1C1)

(0 and false are interchangeable here.)

But I think you got your question backward. The default is true (or absolute
references).



Matt said:
With the FormulaR1C1 function, the
LambdaRange.Address(referencestyle:=xlR1C1) gives a range that is not in the
form $P$8:$P$64008. It's in the form P8:p64008, so when I extend the formula
down, it is a floating range. Any way to lock it in place?

Thanks,
Matt

Dave Peterson said:
Glad you got it working!

Just for completeness...

.Formula = "=SumProduct((" & LambdaRange.Address _
& "> R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"

is a mixture of R1C1 reference style (r[-1]c[-1] and rc[-1] and A1 reference
style (in both .address portions)--and you used .formula instead of
..formulaR1C1.

..FormulaR1C1 = "=SumProduct((" & LambdaRange.Address(referencestyle:=xlR1C1) _
& "> R[-1]C[-1]) * (" _
& LambdaRange.Address(referencestyle:=xlR1C1) & "< RC[-1])))"

Still untested!

Matt said:
Dave,

Thanks for your help! I played around with it and finally got it to work!
I'd actually like to keep the function in the worksheet, so I removed the
.value portion. Here's what I came up with below. Seems the difference
between what I did and what you did was that I did not put the .value portion
on the range("AF" & i). Not exactly sure why that would be the deciding
factor.

Thanks again!
Matt

'Make Lambda and Temp Distribution Plots

Dim LambdaRange As Range
Dim TempRange As Range

Sheets("Runlog").Select

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.8
Range("AH8").Value = 20

'Define Ranges within runlog
Set LambdaRange = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))


'create temp and lambda table and count # of instances data fell between
ranges
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).Formula = "=SUMPRODUCT((" & LambdaRange.Address & ">=
" & Range("AF" & i - 1) & ") * (" & LambdaRange.Address & "<=" & Range("AF" &
i) & "))"
Next

For i = 9 To NumTRows
Range("AH" & i).Value = Range("AH" & i - 1).Value + TDist
Range("AI" & i).Formula = "=SUMPRODUCT((" & TempRange.Address & ">= "
& Range("AH" & i - 1) & ") * (" & TempRange.Address & "<=" & Range("AH" & i)
& "))"
Next

Not sure what was dif
Click to expand...
Click to expand...
Click to expand...
 
M

Matt S

Dave,

Thank you so much for your help! One more issue, which may need its own
post... my files end up being larger than the row limit for excel, so these
lines of code take over 3 minutes to perform as it has to go through two
complete excel columns. Do you know a way I can make this calculation go a
little faster? Maybe I could do it for the first line then autofill it
downward? Here is the code that you've helped make work:



'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.5
Range("AJ8").Value = 20

'Copy lambda and temp values from the second sheet to the first
Sheets("Runlog2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("P8:p" & LastRow & ", R8:R" & LastRow & ", N8:N" & LastRow).Copy

Sheets("Runlog1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AN8").PasteSpecial Paste:=xlPasteValues

'Define Ranges within runlogs
Set FrontLambda = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set BackLambda = Range(Cells(8, "R"), Cells(LastRow, "R"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))

Set FrontLambda2 = Range(Cells(8, "AO"), Cells(LastRow, "AO"))
Set BackLambda2 = Range(Cells(8, "AP"), Cells(LastRow, "AP"))
Set TempRange2 = Range(Cells(8, "AN"), Cells(LastRow, "AN"))

'create temp and lambda table and count # of instances data fell between
ranges for both sheets
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see:

'http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).FormulaR1C1 = "=SUMPRODUCT((" &
FrontLambda.Address(1, 1, xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & FrontLambda2.Address(1, 1,
xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"

Range("AH" & i).FormulaR1C1 = "=SUMPRODUCT((" & BackLambda.Address(1,
1, xlR1C1) & _
">= R[-1]C[-2]) * (" & BackLambda.Address(1, 1, xlR1C1) & _
"<= RC[-2])) + SUMPRODUCT((" & BackLambda2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-2]) * (" & BackLambda2.Address(1, 1, xlR1C1) & "<=
RC[-2]))"
Next

For i = 9 To NumTRows
Range("AJ" & i).Value = Range("AJ" & i - 1).Value + TDist
Range("AK" & i).FormulaR1C1 = "=SUMPRODUCT((" & TempRange.Address(1,
1, xlR1C1) & _
">= R[-1]C[-1]) * (" & TempRange.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & TempRange2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-1]) * (" & TempRange2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"
Next
 
D

Dave Peterson

I'd try turning calculation to manual, then doing all those formulas. Then when
you're done with all of them, turn caculation back to automatic (or what you had
before).

Matt said:
Dave,

Thank you so much for your help! One more issue, which may need its own
post... my files end up being larger than the row limit for excel, so these
lines of code take over 3 minutes to perform as it has to go through two
complete excel columns. Do you know a way I can make this calculation go a
little faster? Maybe I could do it for the first line then autofill it
downward? Here is the code that you've helped make work:

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.5
Range("AJ8").Value = 20

'Copy lambda and temp values from the second sheet to the first
Sheets("Runlog2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("P8:p" & LastRow & ", R8:R" & LastRow & ", N8:N" & LastRow).Copy

Sheets("Runlog1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AN8").PasteSpecial Paste:=xlPasteValues

'Define Ranges within runlogs
Set FrontLambda = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set BackLambda = Range(Cells(8, "R"), Cells(LastRow, "R"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))

Set FrontLambda2 = Range(Cells(8, "AO"), Cells(LastRow, "AO"))
Set BackLambda2 = Range(Cells(8, "AP"), Cells(LastRow, "AP"))
Set TempRange2 = Range(Cells(8, "AN"), Cells(LastRow, "AN"))

'create temp and lambda table and count # of instances data fell between
ranges for both sheets
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see:

'http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).FormulaR1C1 = "=SUMPRODUCT((" &
FrontLambda.Address(1, 1, xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & FrontLambda2.Address(1, 1,
xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"

Range("AH" & i).FormulaR1C1 = "=SUMPRODUCT((" & BackLambda.Address(1,
1, xlR1C1) & _
">= R[-1]C[-2]) * (" & BackLambda.Address(1, 1, xlR1C1) & _
"<= RC[-2])) + SUMPRODUCT((" & BackLambda2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-2]) * (" & BackLambda2.Address(1, 1, xlR1C1) & "<=
RC[-2]))"
Next

For i = 9 To NumTRows
Range("AJ" & i).Value = Range("AJ" & i - 1).Value + TDist
Range("AK" & i).FormulaR1C1 = "=SUMPRODUCT((" & TempRange.Address(1,
1, xlR1C1) & _
">= R[-1]C[-1]) * (" & TempRange.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & TempRange2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-1]) * (" & TempRange2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"
Next
Click to expand...
 
M

Matt S

Dave,

I tried the manual calculation and also previously had screenupdating off.
I unfortunately do not see a faster performance time with these calculations
in manual. Do you think the SumProduct function is best for these large
datasets or is there another function I should be looking in to using?

Thanks,
Matt

Dave Peterson said:
I'd try turning calculation to manual, then doing all those formulas. Then when
you're done with all of them, turn caculation back to automatic (or what you had
before).

Matt said:
Dave,

Thank you so much for your help! One more issue, which may need its own
post... my files end up being larger than the row limit for excel, so these
lines of code take over 3 minutes to perform as it has to go through two
complete excel columns. Do you know a way I can make this calculation go a
little faster? Maybe I could do it for the first line then autofill it
downward? Here is the code that you've helped make work:

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.5
Range("AJ8").Value = 20

'Copy lambda and temp values from the second sheet to the first
Sheets("Runlog2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("P8:p" & LastRow & ", R8:R" & LastRow & ", N8:N" & LastRow).Copy

Sheets("Runlog1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AN8").PasteSpecial Paste:=xlPasteValues

'Define Ranges within runlogs
Set FrontLambda = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set BackLambda = Range(Cells(8, "R"), Cells(LastRow, "R"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))

Set FrontLambda2 = Range(Cells(8, "AO"), Cells(LastRow, "AO"))
Set BackLambda2 = Range(Cells(8, "AP"), Cells(LastRow, "AP"))
Set TempRange2 = Range(Cells(8, "AN"), Cells(LastRow, "AN"))

'create temp and lambda table and count # of instances data fell between
ranges for both sheets
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see:

'http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).FormulaR1C1 = "=SUMPRODUCT((" &
FrontLambda.Address(1, 1, xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & FrontLambda2.Address(1, 1,
xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"

Range("AH" & i).FormulaR1C1 = "=SUMPRODUCT((" & BackLambda.Address(1,
1, xlR1C1) & _
">= R[-1]C[-2]) * (" & BackLambda.Address(1, 1, xlR1C1) & _
"<= RC[-2])) + SUMPRODUCT((" & BackLambda2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-2]) * (" & BackLambda2.Address(1, 1, xlR1C1) & "<=
RC[-2]))"
Next

For i = 9 To NumTRows
Range("AJ" & i).Value = Range("AJ" & i - 1).Value + TDist
Range("AK" & i).FormulaR1C1 = "=SUMPRODUCT((" & TempRange.Address(1,
1, xlR1C1) & _
">= R[-1]C[-1]) * (" & TempRange.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & TempRange2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-1]) * (" & TempRange2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"
Next
Click to expand...
Click to expand...
 
D

Dave Peterson

Instead of lots of =sumproduct() formulas, I like to use pivottables.

It builds a summary on a different worksheet, but updates really quickly (but
not automatically).

Matt said:
Dave,

I tried the manual calculation and also previously had screenupdating off.
I unfortunately do not see a faster performance time with these calculations
in manual. Do you think the SumProduct function is best for these large
datasets or is there another function I should be looking in to using?

Thanks,
Matt

Dave Peterson said:
I'd try turning calculation to manual, then doing all those formulas. Then when
you're done with all of them, turn caculation back to automatic (or what you had
before).

Matt said:
Dave,

Thank you so much for your help! One more issue, which may need its own
post... my files end up being larger than the row limit for excel, so these
lines of code take over 3 minutes to perform as it has to go through two
complete excel columns. Do you know a way I can make this calculation go a
little faster? Maybe I could do it for the first line then autofill it
downward? Here is the code that you've helped make work:

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.5
Range("AJ8").Value = 20

'Copy lambda and temp values from the second sheet to the first
Sheets("Runlog2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("P8:p" & LastRow & ", R8:R" & LastRow & ", N8:N" & LastRow).Copy

Sheets("Runlog1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AN8").PasteSpecial Paste:=xlPasteValues

'Define Ranges within runlogs
Set FrontLambda = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set BackLambda = Range(Cells(8, "R"), Cells(LastRow, "R"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))

Set FrontLambda2 = Range(Cells(8, "AO"), Cells(LastRow, "AO"))
Set BackLambda2 = Range(Cells(8, "AP"), Cells(LastRow, "AP"))
Set TempRange2 = Range(Cells(8, "AN"), Cells(LastRow, "AN"))

'create temp and lambda table and count # of instances data fell between
ranges for both sheets
'Resource for understanding SUMPRODUCT use is here:
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see:

'http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).FormulaR1C1 = "=SUMPRODUCT((" &
FrontLambda.Address(1, 1, xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & FrontLambda2.Address(1, 1,
xlR1C1) & _
">= R[-1]C[-1]) * (" & FrontLambda2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"

Range("AH" & i).FormulaR1C1 = "=SUMPRODUCT((" & BackLambda.Address(1,
1, xlR1C1) & _
">= R[-1]C[-2]) * (" & BackLambda.Address(1, 1, xlR1C1) & _
"<= RC[-2])) + SUMPRODUCT((" & BackLambda2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-2]) * (" & BackLambda2.Address(1, 1, xlR1C1) & "<=
RC[-2]))"
Next

For i = 9 To NumTRows
Range("AJ" & i).Value = Range("AJ" & i - 1).Value + TDist
Range("AK" & i).FormulaR1C1 = "=SUMPRODUCT((" & TempRange.Address(1,
1, xlR1C1) & _
">= R[-1]C[-1]) * (" & TempRange.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & TempRange2.Address(1, 1, xlR1C1)
& _
">= R[-1]C[-1]) * (" & TempRange2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"
Next
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

YYY-MM-DD format is changing when convert data to Flat File 3
Need a little help with a custom function 1
Using VBA to Copy Ranges to a PreSet Dim formula AND Cell Propertys. 0
Endless looping with Worksheet_change event (XL2003) 3
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
sumproduct in UDF 7
copy entire row and paste values only to another sheet 12
I need to change from "=E7" to (equals the current value of E7) 3

Top