Sluggish Spreadsheet

brenner

Board Regular
Joined
Sep 13, 2007
Messages
96
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?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

Board Regular
Joined
Sep 13, 2007
Messages
96
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top