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

shift-del · Aug 23, 2022

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:

Whole Column References can get you in TROUBLE with Excel - Excel Campus

Should you use Whole Column References in Excel? While there are some advantages, the disadvantages make it risky. Find out why.

www.excelcampus.com

3. Use a pivot table.

Kaper · Aug 23, 2022

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

keef2 · Aug 23, 2022

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!

keef2 · Aug 23, 2022

shift-del said:
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:

Whole Column References can get you in TROUBLE with Excel - Excel Campus

Should you use Whole Column References in Excel? While there are some advantages, the disadvantages make it risky. Find out why.

www.excelcampus.com

3. Use a pivot table.

Problem with 0's is it then uses that for average calculations which is inaccurate.

keef2 · Aug 23, 2022

So for a Pivot table how do I get Months to show up and add all values for just those specific months?

shift-del · Aug 23, 2022

keef2 said:
Problem with 0's is it then uses that for average calculations which is inaccurate.

If there is no sales in one month then it should reflect in the average value.

keef2 said:
So for a Pivot table how do I get Months to show up and add all values for just those specific months?

analyse sales sales with pivot table - Google Search

Fluff · Aug 23, 2022

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))

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))

Let me see if this would speed it up thanks!

Fluff · Aug 23, 2022

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

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

keef2

Board Regular

Excel Facts

shift-del

Well-known Member

Whole Column References can get you in TROUBLE with Excel - Excel Campus

Kaper

Active Member

keef2

Board Regular

keef2

Board Regular

Whole Column References can get you in TROUBLE with Excel - Excel Campus

keef2

Board Regular

shift-del

Well-known Member

Fluff

MrExcel MVP, Moderator

keef2

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

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

Board Regular

Excel Facts

Well-known Member

Active Member

Board Regular

Board Regular

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

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