| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct troubleshooting (new to me)
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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) Matt S wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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" wrote: 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) Matt S wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 he '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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 S wrote: 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 he '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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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" wrote: 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 S wrote: 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 he '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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 S wrote: 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" wrote: 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 S wrote: 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 he '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 -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 he 'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see: 'http://www.microsoft.com/office/community/en-us/wizard.mspx?query=sumproduct&w=1&type=question&dg= &cat=en-us-excel&lang=en&cr=US&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 S wrote: 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 he 'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see: 'http://www.microsoft.com/office/community/en-us/wizard.mspx?query=sumproduct&w=1&type=question&dg= &cat=en-us-excel&lang=en&cr=US&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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" wrote: 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 S wrote: 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 he 'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see: 'http://www.microsoft.com/office/community/en-us/wizard.mspx?query=sumproduct&w=1&type=question&dg= &cat=en-us-excel&lang=en&cr=US&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&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 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 S wrote: 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" wrote: 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 S wrote: 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 he 'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples Also see: 'http://www.microsoft.com/office/community/en-us/wizard.mspx?query=sumproduct&w=1&type=question&dg= &cat=en-us-excel&lang=en&cr=US&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Troubleshooting | Excel Discussion (Misc queries) | |||
| Need Help Troubleshooting | Excel Programming | |||
| SUMPRODUCT troubleshooting | Excel Worksheet Functions | |||
| troubleshooting | Setting up and Configuration of Excel | |||
| DDE Troubleshooting | Excel Programming | |||
Linear Mode
