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

keef2 · Aug 23, 2022

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

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Monthly Sales

3

January

Febuary

March

April

May

June

July

August

September

October

November

December

TOTAL SALES

4

2012

5

6

7

8

9

10

11

12

13

14

16

17

AVERAGE

18

19

6

Yr AVG

20

2020

21

22

Compare Years

23

2021

2020

Compare Yrs

SALES SUMMARY

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

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

keef2 · Aug 23, 2022

Fluff said:
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))

working Sales Table VBA - Test.xlsm

D

4

#VALUE!

SALES SUMMARY

Cell Formulas
Range	Formula
D4	D4	=LET(f,SUM(FILTER(MASTER!$G:$G,(MONTH(MASTER!$E:$E)=D$2)*(YEAR(MASTER!$E:$E)=$C4),"")),IF(f="","",f))

Comes up with an error?

keef2 · Aug 23, 2022

Fluff said:
It should be significantly faster, but it's still best to avoided whole column references (which are never needed).

how to you ensure you capture all data without whole column references? So if a user continues to add data to the master sheet how do you make sure that the range is always capturing all the data?

Dave Patton · Aug 23, 2022

You could try a Pivot Table.

2 quick examples with formulas
one uses a helper column

Sumifs.xlsm

A

B

C

D

E

1

Helper

Dates

Amounts

2

201201

01-01-12

1000000

3

201201

02-01-12

2000000

4

201201

04-01-12

3000000

5

201202

02-02-12

4000000

6

Master

Cell Formulas
Range	Formula
A2:A5	A2	=YEAR(D2)&TEXT(MONTH(D2),"00")

Sumifs.xlsm

C

D

E

3

January

February

4

2012

6,000,000.00

4,000,000.00

Sales Summary

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT(Master!$E$2:$E$10,--(Master!$D$2:$D$10-DAY(Master!$D$2:$D$10)+1=DATE(C4,MONTH(1&D3),1)))
E4	E4	=SUMPRODUCT(Master!$E$2:$E$10,--(Master!A2:A10=C4&TEXT(MONTH(E3&1),"00")))

Fluff · Aug 24, 2022

keef2 said:
how to you ensure you capture all data without whole column references?

Just set the range to something larger than you would ever expect to use without going over the top, like

Excel Formula:

=LET(f,SUM(FILTER(Master!$G2:$G10000,(MONTH(Master!$E2:$E10000)=D$2)*(YEAR(Master!$E2:$E10000)=$C4),"")),IF(f="","",f))

You are probably getting the error because you have text values in col E

keef2 · Aug 24, 2022

Fluff said:
Just set the range to something larger than you would ever expect to use without going over the top, like

Excel Formula:

=LET(f,SUM(FILTER(Master!$G2:$G10000,(MONTH(Master!$E2:$E10000)=D$2)*(YEAR(Master!$E2:$E10000)=$C4),"")),IF(f="","",f))

You are probably getting the error because you have text values in col E

Yo Fluff! your a rock star LET function is pretty slick and extremely fast! Thank you so much for your help.

keef2 · Aug 24, 2022

Dave Patton said:
You could try a Pivot Table.

2 quick examples with formulas
one uses a helper column

Sumifs.xlsm
A B C D E
1 Helper Dates Amounts
2 201201 01-01-12 1000000
3 201201 02-01-12 2000000
4 201201 04-01-12 3000000
5 201202 02-02-12 4000000
6
Master
Cell Formulas
Range Formula
A2:A5 A2 =YEAR(D2)&TEXT(MONTH(D2),"00")

Sumifs.xlsm
C D E
3 January February
4 2012 6,000,000.00 4,000,000.00
Sales Summary
Cell Formulas
Range Formula
D4 D4 =SUMPRODUCT(Master!$E$2:$E$10,--(Master!$D$2:$D$10-DAY(Master!$D$2:$D$10)+1=DATE(C4,MONTH(1&D3),1)))
E4 E4 =SUMPRODUCT(Master!$E$2:$E$10,--(Master!A2:A10=C4&TEXT(MONTH(E3&1),"00")))

Dave, Thank you for the examples, pivot tables are just something id rather stay away from. These are great alternatives. I will test them out vs the LET function but right now its cruisin' pretty good! Thanks again for the time and suggestions.

Fluff · Aug 24, 2022

Glad we could help & thanks for the feedback.

keef2 · Aug 24, 2022

shift-del said:
If there is no sales in one month then it should reflect in the average value.

analyse sales sales with pivot table - Google Search

Shift DEL thanks for the suggestion again while i am very weak at Pivot tables this may be a good opportunity to play around with them. Thanks again! I'll make a prototype to see if it makes managing the sheet more reasonable down the road. Cheers!

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

keef2

Board Regular

keef2

Board Regular

Excel Facts

keef2

Board Regular

Dave Patton

Well-known Member

Fluff

MrExcel MVP, Moderator

keef2

Board Regular

keef2

Board Regular

Fluff

MrExcel MVP, Moderator

keef2

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Board Regular

Excel Facts

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock