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.
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.
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4:O14 | D4 | =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:P14 | P4 | =IF(SUM(D4:O4)=0,"",SUM(D4:O4)) |
Q4:Q14 | Q4 | =C4 |
C5:C14 | C5 | =C4+1 |
D17:P17 | D17 | =AVERAGE(D4:D15) |
Q17 | Q17 | =+C17 |
D19:P19 | D19 | =AVERAGE(OFFSET(D3,COUNT(D4:D15),0,-$B$19)) |
Q19 | Q19 | =CONCATENATE(B19, " ",C19) |
C20 | C20 | =CONCATENATE("Vs"," ",B19," ",C19) |
D20 | D20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,2,FALSE)-D19,"") |
E20 | E20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,3,FALSE)-E19,"") |
F20 | F20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,4,FALSE)-F19,"") |
G20 | G20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,5,FALSE)-G19,"") |
H20 | H20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,6,FALSE)-H19,"") |
I20 | I20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,7,FALSE)-I19,"") |
J20 | J20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,8,FALSE)-J19,"") |
K20 | K20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,9,FALSE)-K19,"") |
L20 | L20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,10,FALSE)-L19,"") |
M20 | M20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,11,FALSE)-M19,"") |
N20 | N20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,12,FALSE)-N19,"") |
O20 | O20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,13,FALSE)-O19,"") |
P20 | P20 | =IFERROR(VLOOKUP($B$20,$C$4:$P$15,14,FALSE)-P19,"") |
Q20 | Q20 | =CONCATENATE("Vs ",B20) |
D23 | D23 | =VLOOKUP($B$23,$C$4:$P$15,2,FALSE)-VLOOKUP($C$23,$C$4:$P$15,2,FALSE) |
E23 | E23 | =VLOOKUP($B$23,$C$4:$P$15,3,FALSE)-VLOOKUP($C$23,$C$4:$P$15,3,FALSE) |
F23 | F23 | =VLOOKUP($B$23,$C$4:$P$15,4,FALSE)-VLOOKUP($C$23,$C$4:$P$15,4,FALSE) |
G23 | G23 | =VLOOKUP($B$23,$C$4:$P$15,5,FALSE)-VLOOKUP($C$23,$C$4:$P$15,5,FALSE) |
H23 | H23 | =VLOOKUP($B$23,$C$4:$P$15,6,FALSE)-VLOOKUP($C$23,$C$4:$P$15,6,FALSE) |
I23 | I23 | =VLOOKUP($B$23,$C$4:$P$15,7,FALSE)-VLOOKUP($C$23,$C$4:$P$15,7,FALSE) |
J23 | J23 | =VLOOKUP($B$23,$C$4:$P$15,8,FALSE)-VLOOKUP($C$23,$C$4:$P$15,8,FALSE) |
K23 | K23 | =VLOOKUP($B$23,$C$4:$P$15,9,FALSE)-VLOOKUP($C$23,$C$4:$P$15,9,FALSE) |
L23 | L23 | =VLOOKUP($B$23,$C$4:$P$15,10,FALSE)-VLOOKUP($C$23,$C$4:$P$15,10,FALSE) |
M23 | M23 | =VLOOKUP($B$23,$C$4:$P$15,11,FALSE)-VLOOKUP($C$23,$C$4:$P$15,11,FALSE) |
N23 | N23 | =VLOOKUP($B$23,$C$4:$P$15,12,FALSE)-VLOOKUP($C$23,$C$4:$P$15,12,FALSE) |
O23 | O23 | =VLOOKUP($B$23,$C$4:$P$15,13,FALSE)-VLOOKUP($C$23,$C$4:$P$15,13,FALSE) |
P23 | P23 | =VLOOKUP($B$23,$C$4:$P$15,14,FALSE)-VLOOKUP($C$23,$C$4:$P$15,14,FALSE) |
Workbook Metrics for time.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
3 | Physical Environment | Profile: | 8/23/2022 13:46 | \\192.168.1.2\userhomedir$\kmiles\Desktop\working Sales Table VBA - No Metrics backup.xlsm | ||||||
4 | RAM(MB) | 4,195 | 15,293 | Workbook Settings | ||||||
5 | Swap File(MB) | 6,783 | 17,597 | Shared | Decimal Places | Protection | ||||
6 | Excel Memory(MB) | 2,059 | 131,072 | Shared | FALSE | Fixed | FALSE | Structure | ||
7 | FileSize(K) | 918 | Changes | TRUE | Places | 2 | Worksheets | |||
8 | Operating System | Windows 10 .19042 x64 | Saved | FALSE | Backup | TRUE | Password | |||
9 | MHZ x Cores | 3194 x 8 | Format | XMLWorkbookMacro | Styles | 50 | Views | |||
10 | Excel Version | Excel 365.15427 x64 | Environment Counts | XLB/Qat (K) | Com Addins | |||||
11 | XL Calc Engine | 191029 Dyn Array | Temp | 75 | VBE | 9 | Excel Addins | |||
12 | ||||||||||
13 | Single-Threaded WorkSheet Formulas Profile for | SALES SUMMARY | ||||||||
14 | Formula | Formula | Microsecs | Total Millisecs | Formula % | |||||
15 | Address | Flags | Unique Formulas | Count | /Formula | This Formula | Area/Sheet | of Sheet Time | ||
16 | $D$4:$O$15 | B-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))))) | 144 | 1,900,979.58 | 273,741.06 | 273,794.12 | 100% | ||
17 | $K$28:$K$39 | B-N-M | =SUMIFS('SALES BY PM'!$O:$O,'SALES BY PM'!$A:$A,H28) | 12 | 351.78 | 4.22 | 273,794.12 | 0% | ||
18 | $Q$28:$Q$39 | B-N-M | =SUMIFS('SALES BY PM'!$W:$W,'SALES BY PM'!$A:$A,H28) | 12 | 349.16 | 4.19 | 273,794.12 | 0% | ||
19 | $N$28:$N$39 | B-N-M | =SUMIFS('SALES BY PM'!$T:$T,'SALES BY PM'!$A:$A,H28) | 12 | 347.74 | 4.17 | 273,794.12 | 0% | ||
20 | $M$28:$M$39 | B-N-M | =SUMIFS('SALES BY PM'!$S:$S,'SALES BY PM'!$A:$A,H28) | 12 | 343.95 | 4.13 | 273,794.12 | 0% | ||
21 | $I$28:$I$39 | B-N-M | =SUMIFS('SALES BY PM'!$L:$L,'SALES BY PM'!$A:$A,'SALES SUMMARY'!H28) | 12 | 341.09 | 4.09 | 273,794.12 | 0% | ||
22 | $L$28:$L$39 | B-N-M | =SUMIFS('SALES BY PM'!$R:$R,'SALES BY PM'!$A:$A,H28) | 12 | 340.01 | 4.08 | 273,794.12 | 0% | ||
FastXLFuncs1 |
Workbook Metrics for time.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
98 | Local | Function | English | Function | ||
99 | Function Name | Flags | Function Name | Count | ||
100 | IF | B-N-M | IF | 744 | ||
101 | SUM | B-N-M | SUM | 344 | ||
102 | ISNUMBER | B-N-M | ISNUMBER | 288 | ||
103 | MONTH | B-N-M | MONTH | 288 | ||
104 | YEAR | B-N-M | YEAR | 288 | ||
105 | ROW | B-N-M | ROW | 3 | ||
106 | ROWS | B-N-M | ROWS | 1 | ||
107 | AVERAGE | B-N-M | AVERAGE | 28 | ||
108 | OFFSET | B-V-M | OFFSET | 13 | ||
109 | COUNT | B-N-M | COUNT | 13 | ||
110 | CONCATENATE | B-N-M | CONCATENATE | 3 | ||
111 | IFERROR | B-N-M | IFERROR | 36 | ||
112 | VLOOKUP | B-N-M | VLOOKUP | 87 | ||
113 | SUMIFS | B-N-M | SUMIFS | 108 | ||
FastXLFuncs1 |