Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
I have this code right now:
For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
Matt
Obviously if you cycle through less items, it will run quicker. If ArrayB is a table on a sheet, you can divide each element of the table by a simple copy - PasteSpecial - Divide. HTH Otto "Matt S" wrote in message ... I have this code right now: For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
The answer is "no" since you are changing the value of the elements.
However, if this is some intermediate step where the values have come from or are going to be placed on a worksheet, you can eliminate the loop in favor of a simple PasteSpecial operation; but you will need to tell us more about what you are doing before we can offer code to you for one of those situations. -- Rick (MVP - Excel) "Otto Moehrbach" wrote in message ... Matt Obviously if you cycle through less items, it will run quicker. If ArrayB is a table on a sheet, you can divide each element of the table by a simple copy - PasteSpecial - Divide. HTH Otto "Matt S" wrote in message ... I have this code right now: For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
The answer is "no" since you are changing the value of the elements.
However, if this is some intermediate step where the values have come from or are going to be placed on a worksheet, you can eliminate the loop in favor of a simple PasteSpecial operation; but you will need to tell us more about what you are doing before we can offer code to you for one of those situations. -- Rick (MVP - Excel) "Matt S" wrote in message ... I have this code right now: For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
Sorry about posting this to your message... I thought I was pointing at the
OP's message. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... The answer is "no" since you are changing the value of the elements. However, if this is some intermediate step where the values have come from or are going to be placed on a worksheet, you can eliminate the loop in favor of a simple PasteSpecial operation; but you will need to tell us more about what you are doing before we can offer code to you for one of those situations. -- Rick (MVP - Excel) "Otto Moehrbach" wrote in message ... Matt Obviously if you cycle through less items, it will run quicker. If ArrayB is a table on a sheet, you can divide each element of the table by a simple copy - PasteSpecial - Divide. HTH Otto "Matt S" wrote in message ... I have this code right now: For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
This is the full code below... it was my solution to my previous post that
nobody was able to help me with. In a nutshell, I define data arrays from ranges on my excel sheet. Then I go about shifting the data 1 row down, calculate this lambda value, and 1compare to the engine lambda by the correlation function. I look for the shift that maximizes the correlation function. The problem is, if I allow 10 or more shifts in data, it's (10+1)^3 calculations that need to happen to fill my arrCorrel array. By far the longest process is calculating the lambda value. Would pasting the calculated lambda somewhere to do the calculations be faster? Thanks, Matt Function DataAdj(LastRow As Double) LR = LastRow - 12 Application.DisplayAlerts = True NumShifts = 10 ReDim arrCorrel(1 To (NumShifts + 1) ^ 4) ReDim arrCO(1 To LR) ReDim arrNOx(1 To LR) ReDim arrHC(1 To LR) ReDim arrO2(1 To LR) ReDim Lambda_calc(1 To LR) ReDim Lambda_engine(1 To LR) ReDim arrayA(1 To LR) ReDim arrayB(1 To LR) ReDim arrayC(1 To LR) ReDim arrayD(1 To LR) ReDim arrayE(1 To LR) ReDim arrayF(1 To LR) ReDim arrLambda(1 To LR) arrCO = Application.Transpose(Range("G13:G" & LastRow)) arrHC = Application.Transpose(Range("F13:F" & LastRow)) arrNOx = Application.Transpose(Range("J13:J" & LastRow)) arrO2 = Application.Transpose(Range("K13:K" & LastRow)) Lambda_engine = Application.Transpose(Range("O13:O" & LastRow)) Count = 1 MaxValue = 0 For i = 0 To NumShifts For j = 0 To NumShifts For k = 0 To NumShifts For l = 0 To NumShifts For ii = 1 To LR - NumShifts arrayA(ii + i) = arrCO(ii + i) / 10 ^ 4 arrayB(ii + j) = arrHC(ii + j) / 10 ^ 4 arrayC(ii + k) = arrNOx(ii + k) / 10 ^ 4 arrayD(ii + l) = arrO2(ii + l) / 10 ^ 4 arrayE(ii) = 4 / 3 * arrayA(ii + i) + 18 * arrayB(ii + j) - (2 * arrayD(ii + l) + arrayC(ii + k)) arrayF(ii) = 0.5 * arrayC(ii + k) + arrayD(ii + l) - (2 / 3 * arrayA(ii + i) + 9 * arrayB(ii + j)) If arrayF(ii) = 0 Then arrLambda(ii) = 1 + arrayF(ii) * 2 / (1 + 0.25 * 1.9 - 0.5 * 0.02) / (100 - 4.79 * arrayF(ii)) Else arrLambda(ii) = 1 - arrayE(ii) * 2 / (1 + 0.25 * 1.9 - 0.5 * 0.02) / (200 + 3.79 * arrayE(ii)) End If If arrLambda(ii) 1.524 Then arrLambda(ii) = 1.524 Else: End If Next ii With Application.WorksheetFunction arrCorrel(Count) = .Correl(.Index(arrLambda, 0), ..Index(Lambda_engine, 0)) End With If arrCorrel(Count) MaxValue Then index_i = i index_j = j index_k = k index_l = l MaxValue = arrCorrel(Count) Else: End If Count = Count + 1 Next l Next k Next j Next i If index_i 0 Then For i = 1 To index_i Range("G13").Delete Shift:=xlUp Next i0 End If Range("G7").Value = index_i If index_j 0 Then For i = 1 To index_j Range("F13").Delete Shift:=xlUp Next i End If Range("F7").Value = index_j If index_k 0 Then For i = 1 To index_k Range("I13:J13").Delete Shift:=xlUp Next i End If Range("I7").Value = index_k If index_l 0 Then For i = 1 To index_l Range("K13").Delete Shift:=xlUp Next i Range("K7").Value = index_l End If Range("J1").Value = MaxValue MaxIndex = Application.WorksheetFunction.Max(index_i, index_j, index_k, index_l) LastRow = LastRow - MaxIndex End Function "Rick Rothstein" wrote: The answer is "no" since you are changing the value of the elements. However, if this is some intermediate step where the values have come from or are going to be placed on a worksheet, you can eliminate the loop in favor of a simple PasteSpecial operation; but you will need to tell us more about what you are doing before we can offer code to you for one of those situations. -- Rick (MVP - Excel) "Matt S" wrote in message ... I have this code right now: For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
Matt S wrote:
This is the full code below... it was my solution to my previous post that nobody was able to help me with. In a nutshell, I define data arrays from ranges on my excel sheet. Then I go about shifting the data 1 row down, calculate this lambda value, and 1compare to the engine lambda by the correlation function. I look for the shift that maximizes the correlation function. The problem is, if I allow 10 or more shifts in data, it's (10+1)^3 calculations that need to happen to fill my arrCorrel array. By far the longest process is calculating the lambda value. Would pasting the calculated lambda somewhere to do the calculations be faster? Thanks, Matt Function DataAdj(LastRow As Double) LR = LastRow - 12 Application.DisplayAlerts = True NumShifts = 10 ReDim arrCorrel(1 To (NumShifts + 1) ^ 4) ReDim arrCO(1 To LR) ReDim arrNOx(1 To LR) ReDim arrHC(1 To LR) ReDim arrO2(1 To LR) ReDim Lambda_calc(1 To LR) ReDim Lambda_engine(1 To LR) ReDim arrayA(1 To LR) ReDim arrayB(1 To LR) ReDim arrayC(1 To LR) ReDim arrayD(1 To LR) ReDim arrayE(1 To LR) ReDim arrayF(1 To LR) ReDim arrLambda(1 To LR) arrCO = Application.Transpose(Range("G13:G" & LastRow)) arrHC = Application.Transpose(Range("F13:F" & LastRow)) arrNOx = Application.Transpose(Range("J13:J" & LastRow)) arrO2 = Application.Transpose(Range("K13:K" & LastRow)) Lambda_engine = Application.Transpose(Range("O13:O" & LastRow)) Count = 1 MaxValue = 0 For i = 0 To NumShifts For j = 0 To NumShifts For k = 0 To NumShifts For l = 0 To NumShifts For ii = 1 To LR - NumShifts arrayA(ii + i) = arrCO(ii + i) / 10 ^ 4 arrayB(ii + j) = arrHC(ii + j) / 10 ^ 4 arrayC(ii + k) = arrNOx(ii + k) / 10 ^ 4 arrayD(ii + l) = arrO2(ii + l) / 10 ^ 4 arrayE(ii) = 4 / 3 * arrayA(ii + i) + 18 * arrayB(ii + j) - (2 * arrayD(ii + l) + arrayC(ii + k)) arrayF(ii) = 0.5 * arrayC(ii + k) + arrayD(ii + l) - (2 / 3 * arrayA(ii + i) + 9 * arrayB(ii + j)) If arrayF(ii) = 0 Then arrLambda(ii) = 1 + arrayF(ii) * 2 / (1 + 0.25 * 1.9 - 0.5 * 0.02) / (100 - 4.79 * arrayF(ii)) VBA tends to be a rather simple minded compiler and in an inner loop like that it probably will not do strength reduction. You could well find that x = x/10^4 has been compiled as x = x / exp(4*log(10)) in which case x = x/10000 will be faster and x*0.0001 faster still but rescaling the problem so you don't need to multiply at all would be fastest of all. Same applies to common sub expressions like 2/(1+0.25*1.9 - 0.5*0.02) worth working it out once outside all the loops! This looks like the sort of correlation problem that is best done in the Fourier domain - particularly so if the reference spectra are contant. Then you get the entire correlation at every shift with a cost 2NlogN transform and some book keeping where N is the length of your array. Regards, Martin Brown |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
On 1/12/2010 12:43 PM, Matt S wrote:
I have this code right now: For ii = 1 to 100 arrayA(ii) = arrayB(ii) / 10^4 Next ii Is there a way to do this calculation faster? Maybe without cycling thru all elements of arrayA? Thanks, Matt Maybe without cycling thru all elements of arrayA? Here's one of a few ways to not "Loop," but it's probably not efficient. I find it only helps when equations get too complicated. Sub Demo() Dim v v = Array(30000, 50000, 70000, 110000, 130000) v = Fx(v) End Sub Private Function Fx(v) ActiveWorkbook.Names.Add "M", v Fx = [M/10^4] ActiveWorkbook.Names("M").Delete End Function = = = = = = = HTH :) Dana DeLouis |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
This looks like the sort of correlation problem that is best done in the Fourier domain - particularly so if the reference spectra are contant. Then you get the entire correlation at every shift with a cost 2NlogN transform and some book keeping where N is the length of your array. Regards, Martin Brown . Martin, can you explain further what you mean when you say it should be in the Fourier domain? Can you give me some references? Thanks, Matt |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster way to do this?
Matt S wrote:
This looks like the sort of correlation problem that is best done in the Fourier domain - particularly so if the reference spectra are contant. Then you get the entire correlation at every shift with a cost 2NlogN transform and some book keeping where N is the length of your array. Martin, can you explain further what you mean when you say it should be in the Fourier domain? Can you give me some references? A convolution with a shift in real space is a (complex number) multiplication in the Fourier domain. The best way depends critically on the lengths of the data and pattern to be matched. For large datasets the FFT method will usually win by a huge margin. There should be plenty of references online and practical but slightly dodgy code to do it on the Numerical Recipes webpage (old edition is free access). http://www.nrbook.com/a/bookcpdf/c13-2.pdf And you get every correlation result in a single complicated operation. You probably need to read the descriptions of FFTs and their version is restricted to power of two length arrays (and you will need to think about the boundary conditions you wish to impose). Worth asking in the numerical analysis group too. They will be rude about NR methods but their algorithms are (mostly) usable just not always the best. Regards, Martin Brown |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can this be done faster? | Excel Discussion (Misc queries) | |||
Help to run faster | Excel Programming | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Is there a faster way | Excel Programming | |||
Which one is faster? | Excel Programming |