Sluggish Spreadsheet

[brenner]

I'm to reduce both the recalculation speed and and size of the workbook. Here's a quick snap shot of my spreadsheet. I've provided the formulas for which I believe are slowing down my sheet. I've also written a short description of the purpose of the formulas, so you understand my end goal.

Let me know if you have any questions.

Thanks!!!

http://drop.io/hidden/ckk4ddszd15ysft/asset/cXVlc3Rpb25zLWpwZw%3D%3D


Here are the formulas in the respective row 4 cells
A=IF(AND(YEAR($A$1)=YEAR(A4),MONTH($A$1)=12),C4,IF(AND(YEAR($A$1)=YEAR(A4),OR(MONTH($A$1)=MONTH(A4),MONTH($A$1)-1=MONTH(A4),MONTH($A$1)-2=MONTH(A4))),C4,0))

B=IF(AND(MONTH($A$1+1)=MONTH(A4),YEAR($A$1)=YEAR(A4)),(SUM(C4:INDEX($C4:C$10584,MIN(COUNTIF($K4:K$10584,K4),12),0))),0)
A-looks at A1 and pulls the last 3 months, A1 will always be a month end
B-looks at A1 and pulls the next 12 months or remaining part of the contract as there are multiple contracts within the same data set
-column K is referenced to determin if the contract will end before 12 months is up.
-row 10584 is the last of my data set, not sure if I can somehow make that dynamic, named range?

 

[BrianB]

In 10 years of finance reporting I never had to write formulas as complicated as this. (probably why I do not fully understand what you are trying to do . The lack of other replies suggests I am not alone.)

The basic aim is to have data contained in simple worksheet tables with headings in row 1. Analyses to cut the data are done in separate sheets. Can then use the Excel options in the Data Menu. Pivot tables are especially powerful - but we do have to set the table up correctly.

For example, in this case it looks like the addition of a "Month" column will make your task a lot easier.

 

[brenner]

Brian,

Thanks for the response. I am eventually using a pivot table to display my information. However, I do not know how I can avoid perhaps overly sophisticated formulas. The pivot table will display all the information, but i need that information to be linked to the current month end and at the same time exclude (turn to zero) certain information. I think of my formula as a kind of on/off switch depending on the date, which I can think of how to duplicate with just the pivot table feature alone. I'll continue to work and tinker. appreciate your response though.

 

[jasonb75]

This "might" be slightly faster for the first formula,

=IF(YEAR($A$1)=YEAR(A4),OR(MONTH($A$1)=12,ISNUMBER(MATCH(MONTH($A$1)-MONTH(A4),{0,1,2},0)))*C4,0)

For the second (which looks like the bigger cause of slow calculation) your thought of a dynamic range is probably the best way to try and improve efficiency, some good info on that here http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[BrianB]

It is easier to make additional columns in the raw data. The formulas become simpler because we deal with individual records. Usually just a simple IF(). In your case suggesting that you want some values to be zero for the summary.

 

[jasonb75]

=IF(AND(YEAR($A$1)=YEAR(A4),MONTH($A$1)=12),C4,IF(AND(YEAR($A$1)=YEAR(A4),OR(MONTH($A$1)=MONTH(A4),MONTH($A$1)-1=MONTH(A4),MONTH($A$1)-2=MONTH(A4))),C4,0))

looks at A1 and pulls the last 3 months, A1 will always be a month end

Brenner, I've just been looking over your question again after having some additional thoughts on formula refinement and noticed your description and formula don't match.

December in A1 will return any month in A4 as a match.

The rest of the formula will only allow for current year, Jan date in A4 will only give Jan results, Feb will only give Jan & Feb, to pull the results from Nov / Dec of previous year as appropriate using the current approach would need allowance for year variation, and you would need something like 1-2=11 to be a true result.

Maybe I'm missing something only seeing a small part of your bigger picture, but thought the pointer might be of use in case you hadn't realised.

For a true floating 3 month period you could use

=ISNUMBER(MATCH($A$1,EOMONTH(A4,{0,1,2}),0))*C4

Analysing your second formula, (this is based purely on assumptions and theory) do you realy need to refer to the entire range?

Assuming that column K is relative to the rest of the data, the biggest range this eventually covers is SUM(C4:C15), could the countif part be limited to that range from the start or would it miss other relevant records in the table?

=IF(AND(MONTH($A$1+1)=MONTH(A4),YEAR($A$1)=YEAR(A4)),(SUM(C4:INDEX(C4:C15,MIN(COUNTIF(K4:K15,K4),12),0))),0)

If that's not going to work for your dataset then maybe some trickery to induce part-time recalculation on the heavy formula, assess this suggestion first to see if it might casue other issues.

In excel options enable iterative calculation.

in U4

=IF($A$2,IF(AND(MONTH($A$1+1)=MONTH(A4),YEAR($A$1)=YEAR(A4)),(SUM(C4:INDEX($C4:C$10584,MIN(COUNTIF($K4:K$10584,K4),12),0))),0),U4)

You will get a circular reference warning, click cancel and fill the formula down the column as needed, everything should now show 0

Now Enter 1 in A2, wait for the sheet to calculate, and clear the 1 from the cell.

This allows you to calculate the heavy formula manually while retaining automatic calculation on the rest.

Hopefully something there is of use.