How to reduce WS size by modifying an equation used more than73,000 times in WS

I have a WS that has daily prices on for over 7 years. In another WS I need to calculate the daily rate of change for the ratio of each item to each other. Example: Items a,b,c,d,e,f...
Step 1. calculate the ratios of: price a/price b, price a/ price c, price a/price d... price f/ price d... price j/price i.
step 2. calculate the daily rate of change for each ratio found from step 1.

Below is what I've used to calculate the price relative to each other
=INDEX('Prices-M'!$A$1:$Q$1913,MATCH($G2,'Prices-M'!$A$1:$A$1913,0),MATCH($A$2,'Prices-M'!$A$1:$P$1,0))/INDEX('Prices-M'!$A$1:$Q$1913,MATCH($G2,'Prices-M'!$A$1:$A$1913,0),MATCH($A$8,'Prices-M'!$A$1:$P$1,0)

On the Prices-M WS Column A has the date sorted from oldest to newest and row A is a header with A1-P1 having each item name in it. Beneath each item name is the price on the day corresponding to the date on that row in column A.

On the current worksheet column G has the same dates sorted oldest to newest and row A is a column header with a description of each ratio. Beneath description is the ratio on the day corresponding to the date on that row in column A based on the description.

Please Login or Register  to view this content.

The daily percentage change of the ratios is calculated as

Please Login or Register  to view this content.

dragged down and across the matrix.

The workbook is now 11 MB as there are other pages with formulas. Any ideas on how to reduce the size and increase the speed of this code?
Thanks,
Doug

Hi,

I guess following steps can help you to reduce the WB size and increase the preformance.

1) Clean Up the Workbook: If there are any Used ranges in the workbook then DELETE it.
2) Dont use Preformatted excel. Only format the number of rows which are required to be formatted and style.
3) Delete the unused formulas and references.
4) Avoid Volatile functions and set calculation of formula to Manual rather than Automatic.
5) Try to delete the data if not required


Regards,
Paresh J

To add to what Paresh has said, I assume that the majority of the data is historic. Consequently, having calculated the values, you can Copy the data and Paste Special | Values to remove much of the calculation overhead.

I'd suggest that you leave maybe a week's worth of formula in case the data changes.

Together with removing blank and/or pre-prepared rows, formatting and Conditional Formatting, it should reduce the size of the workbook significantly.

Regards, TMS

You use this twice in the formula:

Formula:

Please Login or Register  to view this content.

So, if you added a Helper column and put

Formula:

Please Login or Register  to view this content.

in it and copied it down, that would save some calculations. Or you could maybe put that formula in a Named Range and refer to it. That would save creating and managing the Helper Column.

And it may be worth looking at the MATCH for the column and seeing if there is a fixed relationship. In other words, can you do that calculation once and adjust it by adding a fixed amount to it.

Bit in the dark really because I can't visualise your data.

Regards, TMS

The column reference $G in the cell reference $G2 is absolute as are the lookup references. Presumably, that is to allow the formula to be dragged across. On that basis, you would only need one helper column to cater for that part of the formula.

But, you're looking for suggestions. That was a suggestion. Do with it what you will.