#### brenner

##### Board Regular
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?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### BrianB

##### Well-known Member
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
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
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
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
=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.

Replies
2
Views
369
Replies
1
Views
383
Replies
3
Views
386
Replies
7
Views
366
Replies
6
Views
561 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,982
Messages
5,834,704
Members
430,310
Latest member
Excelorate3305 ### 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.

### Which adblocker are you using?    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

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