Suggestions on how to re-write this formula for faster calculations

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have this formula that is in the below table D4:O15 that takes quite a longtime to calculate. I am curious if anyone has other options or ways to calculate faster. I also ran some analysis to understand time constraints and other metrics. Any help in revising this formula to make things work faster would be greatly appreciated.

working Sales Table VBA - No Metrics backup.xlsm
BCDEFGHIJKLMNOPQ
1Monthly Sales
3 January Febuary March April May June July August September October November December TOTAL SALES
42012$ 217,787$ 714,700$ 80,019$ 7,908,552$ 3,674,515$ 522,802$ 2,398,672$ 1,251,678$ 28,843$ 17,573$ 9,400$ 847,565$ 17,672,1062012
52013$ 3,093,415$ 1,090,780$ 1,623,840$ 1,796,440$ 1,717,774$ 1,044,612$ 1,825,158$ 3,733,815$ 637,388$ 869,792$ 2,370,985$ 823,075$ 20,627,0742013
62014$ 2,078,895$ 2,626,000$ 6,653,426$ 36,380$ 2,859,745$ 831,390$ 1,937,547$ 3,483,615$ 2,140,838$ 647,872$ 1,137,422$ 67,600$ 24,500,7302014
72015$ 2,533,585$ 2,091,747$ 4,494,199$ 1,702,275$ 1,005,674$ 2,167,017$ 7,680,620$ 110,592$ 3,045,928$ 10,878,519$ 95,300$ 3,564,216$ 39,369,6722015
82016$ 409,465$ 1,832,825$ 3,244,407$ 3,904,514$ 2,306,938$ 5,429,274$ 843,556$ 8,298,244$ 4,035,878$ 1,192,207$ 7,591,000$ 92,750$ 39,181,0582016
92017$ 3,465,280$ 1,513,817$ 6,584,535$ 879,800$ 1,218,100$ 2,192,697$ 298,450$ 3,302,787$ 2,806,055$ 3,908,009$ 458,937$ 1,412,888$ 28,041,3552017
102018$ 4,351,558$ 270,195$ 2,116,150$ 5,489,089$ 2,985,495$ 2,456,650$ 8,331,072$ 2,442,865$ 3,261,885$ 367,150$ 304,861$ 3,049,779$ 35,426,7492018
112019$ 113,838$ 7,071,370$ 1,380,520$ 747,983$ 5,226,733$ 1,606,520$ 7,148,845$ 1,351,090$ 792,577$ 7,278,820$ 4,818,686$ 2,516,597$ 40,053,5792019
122020$ 1,057,457$ 5,948,478$ 2,712,030$ 1,014,750$ 1,511,408$ 1,182,030$ 4,516,640$ 3,857,360$ 2,769,944$ 1,685,050$ 1,704,698$ 2,772,694$ 30,732,5392020
132021$ 4,090,350$ 2,652,478$ 9,143,516$ 9,014,203$ 6,060,405$ 2,350$ 8,571,295$ 11,607,380$ 535,470$ 13,199,409$ 4,606,389$ 383,525$ 69,866,7702021
142022$ 8,128,763$ 5,401,750$ 3,765,450$ 12,984,905$ 7,812,055$ 319,975$ 500,800$ 97,500 $ 39,011,1982022
16
17AVERAGE$ 2,685,490$ 2,837,649$ 3,799,827$ 4,134,445$ 3,307,167$ 1,614,120$ 4,004,787$ 3,594,266$ 2,005,481$ 4,004,440$ 2,309,768$ 1,553,069$ 34,952,985AVERAGE
18
196Yr AVG$ 3,534,541$ 3,809,681$ 4,283,700$ 5,021,788$ 4,135,699$ 1,293,370$ 4,894,517$ 3,776,497$ 2,366,968$ 4,605,108$ 3,247,429$ 1,704,706$ 40,522,0326 Yr AVG
202020Vs 6 Yr AVG$ (2,477,084)$ 2,138,797$ (1,571,670)$ (4,007,038)$ (2,624,291)$ (111,340)$ (377,877)$ 80,863$ 402,976$ (2,920,058)$ (1,542,731)$ 1,067,989$ (9,789,493)Vs 2020
21
22Compare Years
2320212020$ 3,032,893$(3,296,000)$ 6,431,486$ 7,999,453$ 4,548,997$ (1,179,680)$ 4,054,655$ 7,750,020$ (2,234,474)$ 11,514,359$ 2,901,691$ (2,389,169)$ 39,134,231Compare Yrs
SALES SUMMARY
Cell Formulas
RangeFormula
D4:O14D4=IF(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))=0,"",(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))))
P4:P14P4=IF(SUM(D4:O4)=0,"",SUM(D4:O4))
Q4:Q14Q4=C4
C5:C14C5=C4+1
D17:P17D17=AVERAGE(D4:D15)
Q17Q17=+C17
D19:P19D19=AVERAGE(OFFSET(D3,COUNT(D4:D15),0,-$B$19))
Q19Q19=CONCATENATE(B19, " ",C19)
C20C20=CONCATENATE("Vs"," ",B19," ",C19)
D20D20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,2,FALSE)-D19,"")
E20E20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,3,FALSE)-E19,"")
F20F20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,4,FALSE)-F19,"")
G20G20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,5,FALSE)-G19,"")
H20H20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,6,FALSE)-H19,"")
I20I20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,7,FALSE)-I19,"")
J20J20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,8,FALSE)-J19,"")
K20K20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,9,FALSE)-K19,"")
L20L20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,10,FALSE)-L19,"")
M20M20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,11,FALSE)-M19,"")
N20N20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,12,FALSE)-N19,"")
O20O20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,13,FALSE)-O19,"")
P20P20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,14,FALSE)-P19,"")
Q20Q20=CONCATENATE("Vs ",B20)
D23D23=VLOOKUP($B$23,$C$4:$P$15,2,FALSE)-VLOOKUP($C$23,$C$4:$P$15,2,FALSE)
E23E23=VLOOKUP($B$23,$C$4:$P$15,3,FALSE)-VLOOKUP($C$23,$C$4:$P$15,3,FALSE)
F23F23=VLOOKUP($B$23,$C$4:$P$15,4,FALSE)-VLOOKUP($C$23,$C$4:$P$15,4,FALSE)
G23G23=VLOOKUP($B$23,$C$4:$P$15,5,FALSE)-VLOOKUP($C$23,$C$4:$P$15,5,FALSE)
H23H23=VLOOKUP($B$23,$C$4:$P$15,6,FALSE)-VLOOKUP($C$23,$C$4:$P$15,6,FALSE)
I23I23=VLOOKUP($B$23,$C$4:$P$15,7,FALSE)-VLOOKUP($C$23,$C$4:$P$15,7,FALSE)
J23J23=VLOOKUP($B$23,$C$4:$P$15,8,FALSE)-VLOOKUP($C$23,$C$4:$P$15,8,FALSE)
K23K23=VLOOKUP($B$23,$C$4:$P$15,9,FALSE)-VLOOKUP($C$23,$C$4:$P$15,9,FALSE)
L23L23=VLOOKUP($B$23,$C$4:$P$15,10,FALSE)-VLOOKUP($C$23,$C$4:$P$15,10,FALSE)
M23M23=VLOOKUP($B$23,$C$4:$P$15,11,FALSE)-VLOOKUP($C$23,$C$4:$P$15,11,FALSE)
N23N23=VLOOKUP($B$23,$C$4:$P$15,12,FALSE)-VLOOKUP($C$23,$C$4:$P$15,12,FALSE)
O23O23=VLOOKUP($B$23,$C$4:$P$15,13,FALSE)-VLOOKUP($C$23,$C$4:$P$15,13,FALSE)
P23P23=VLOOKUP($B$23,$C$4:$P$15,14,FALSE)-VLOOKUP($C$23,$C$4:$P$15,14,FALSE)



Workbook Metrics for time.xlsx
ABCDEFGH
3Physical EnvironmentProfile:8/23/2022 13:46\\192.168.1.2\userhomedir$\kmiles\Desktop\working Sales Table VBA - No Metrics backup.xlsm
4RAM(MB)4,19515,293Workbook Settings
5Swap File(MB)6,78317,597SharedDecimal PlacesProtection
6Excel Memory(MB)2,059131,072SharedFALSEFixedFALSEStructure
7FileSize(K)918ChangesTRUEPlaces2Worksheets
8Operating SystemWindows 10 .19042 x64SavedFALSEBackupTRUEPassword
9MHZ x Cores3194 x 8FormatXMLWorkbookMacroStyles50Views
10 Excel VersionExcel 365.15427 x64Environment Counts XLB/Qat (K)Com Addins
11XL Calc Engine191029 Dyn ArrayTemp75VBE9 Excel Addins
12
13Single-Threaded WorkSheet Formulas Profile for SALES SUMMARY
14FormulaFormulaMicrosecsTotal MillisecsFormula %
15AddressFlags Unique Formulas Count/FormulaThis FormulaArea/Sheetof Sheet Time
16$D$4:$O$15B-N-M=IF(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))=0,"",(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))))1441,900,979.58273,741.06273,794.12100%
17$K$28:$K$39B-N-M=SUMIFS('SALES BY PM'!$O:$O,'SALES BY PM'!$A:$A,H28)12351.784.22273,794.120%
18$Q$28:$Q$39B-N-M=SUMIFS('SALES BY PM'!$W:$W,'SALES BY PM'!$A:$A,H28)12349.164.19273,794.120%
19$N$28:$N$39B-N-M=SUMIFS('SALES BY PM'!$T:$T,'SALES BY PM'!$A:$A,H28)12347.744.17273,794.120%
20$M$28:$M$39B-N-M=SUMIFS('SALES BY PM'!$S:$S,'SALES BY PM'!$A:$A,H28)12343.954.13273,794.120%
21$I$28:$I$39B-N-M=SUMIFS('SALES BY PM'!$L:$L,'SALES BY PM'!$A:$A,'SALES SUMMARY'!H28)12341.094.09273,794.120%
22$L$28:$L$39B-N-M=SUMIFS('SALES BY PM'!$R:$R,'SALES BY PM'!$A:$A,H28)12340.014.08273,794.120%
FastXLFuncs1



Workbook Metrics for time.xlsx
ABCD
98LocalFunction EnglishFunction
99Function NameFlags Function NameCount
100IFB-N-MIF744
101SUMB-N-MSUM344
102ISNUMBERB-N-MISNUMBER288
103MONTHB-N-MMONTH288
104YEARB-N-MYEAR288
105ROWB-N-MROW3
106ROWSB-N-MROWS1
107AVERAGEB-N-MAVERAGE28
108OFFSETB-V-MOFFSET13
109COUNTB-N-MCOUNT13
110CONCATENATEB-N-MCONCATENATE3
111IFERRORB-N-MIFERROR36
112VLOOKUPB-N-MVLOOKUP87
113SUMIFSB-N-MSUMIFS108
FastXLFuncs1
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi

1. You are calculating this expression twice.
Excel Formula:
SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G))))
Just do the calculation once and accept the zeros or use a user defined number format.

2. You are using whole columns. See this blog post why this might be not a good a idea:

3. Use a pivot table.
 
Upvote 0
Seems that what is in D4:O14 (as a matter of fact also column P) could be easily obtained with Pivot Table, which shall be much quicker than the formula for this range. Probably other formulas are not influencing the speed of calculation that much as the one used for D4:O14
 
Upvote 0
Correct, D4:O14 is the formula that eats up all the time. I liked to have full column ranges so no need to mess with it later. However, i will take a look at the article posted above. As for Pivot table I suck with them but will mess with it and see if i can get everything i need. Ill report back asap.

Thanks!
 
Upvote 0
Hi

1. You are calculating this expression twice.
Excel Formula:
SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G))))
Just do the calculation once and accept the zeros or use a user defined number format.

2. You are using whole columns. See this blog post why this might be not a good a idea:

3. Use a pivot table.
Problem with 0's is it then uses that for average calculations which is inaccurate.
 
Upvote 0
So for a Pivot table how do I get Months to show up and add all values for just those specific months?
 
Upvote 0
Upvote 0
As you have 365 you could use let & filter like
Excel Formula:
=LET(f,SUM(FILTER(Master!$G:$G,(MONTH(Master!$E:$E)=D$2)*(YEAR(Master!$E:$E)=$C4),"")),IF(f="","",f))
 
Upvote 0
As you have 365 you could use let & filter like
Excel Formula:
=LET(f,SUM(FILTER(Master!$G:$G,(MONTH(Master!$E:$E)=D$2)*(YEAR(Master!$E:$E)=$C4),"")),IF(f="","",f))
Let me see if this would speed it up thanks!
 
Upvote 0
It should be significantly faster, but it's still best to avoided whole column references (which are never needed).
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top