joeyc
08-21-2008, 09:30 PM
Hi,
I have a problem that I thought was solved here. I mistakenly said the problem was solved. Here is a link to the previous thread, but I will describe the problem down here anyways: http://www.vbaexpress.com/forum/showthread.php?t=21643 Here is a sample of the file I will reference: http://www.megaupload.com/?d=V8MAWQQ5
The problem has to do with the speed of the array formula in cells D2:D3000. Currently, the spreadsheet will freeze once you try to click the 'Clear' button. And even when the clear button did work, it was still rather slow. Is their any way to replace this formula with a simple macro that does the same thing?
Here is the array formula in question. I will try to summarize what it does.
SIGN($C2)*$C2/SUM(IF($A$2:$A$3000=$A2,IF(C2<0,IF($C$2:$C$3000<0,$C$2:$C$3000),IF($C$2:$C$3000>0,$C$2:$C$3000))))
This is what the array formula does. For each row apart of each product it calculates the percentage that row makes up against the total market value of the product for. However, there is one stipulation. If the Market Value for the row in question is greater than 0 it will calculate this percentage against the summation of all rows where the market value is greater than 0. If the Market Value is question is negative it will calculate this percentage against the summation of all row where teh market value is greater than 0.
Once again, the problem is speed. The array formula is quite elegant but I need more speed.
Thank you in advance for any help.
I have a problem that I thought was solved here. I mistakenly said the problem was solved. Here is a link to the previous thread, but I will describe the problem down here anyways: http://www.vbaexpress.com/forum/showthread.php?t=21643 Here is a sample of the file I will reference: http://www.megaupload.com/?d=V8MAWQQ5
The problem has to do with the speed of the array formula in cells D2:D3000. Currently, the spreadsheet will freeze once you try to click the 'Clear' button. And even when the clear button did work, it was still rather slow. Is their any way to replace this formula with a simple macro that does the same thing?
Here is the array formula in question. I will try to summarize what it does.
SIGN($C2)*$C2/SUM(IF($A$2:$A$3000=$A2,IF(C2<0,IF($C$2:$C$3000<0,$C$2:$C$3000),IF($C$2:$C$3000>0,$C$2:$C$3000))))
This is what the array formula does. For each row apart of each product it calculates the percentage that row makes up against the total market value of the product for. However, there is one stipulation. If the Market Value for the row in question is greater than 0 it will calculate this percentage against the summation of all rows where the market value is greater than 0. If the Market Value is question is negative it will calculate this percentage against the summation of all row where teh market value is greater than 0.
Once again, the problem is speed. The array formula is quite elegant but I need more speed.
Thank you in advance for any help.