Calculated Field in Pivot Tables

sophab

New Member
Joined
May 21, 2019
Messages
3
Hello,

I have the below fields in my spreadsheet that I would like to place in a pivot table. I want to calculate the sales turn for the aggregated with the formula (BOH $/Sales $)*# of weeks in the month.

I have tried using an if statement in the calculated field =if(weeks in month>4, (BOH $/Sales $)*5, (BOH $/Sales $)*4) however this ends up multiplying everything by 5. Is there another way to accomplish this? Please advise!

MonthSales $BOH $Weeks in Month
JANUARY$13,443.00$4,9504
FEBRUARY$18,743.00$17,9724
MARCH$21,630.00$19,7255
APRIL$13,981.00$20,2314
AUGUST$23,200.00$19,9044
SEPTEMBER$23,420.00$19,8865
OCTOBER$17,217.00$24,9334
NOVEMBER$21,810.00$27,1264
DECEMBER$52,669.00$46,7235
JANUARY$84.00$904
FEBRUARY$632.00$84
MARCH$1,118.00$2,8405
APRIL$442.00$1,4924
AUGUST$2.00-$244
SEPTEMBER$1,049.00$1,1965
OCTOBER$114.00$1314
NOVEMBER$685.00$184
DECEMBER$1,776.00$1,8385
FEBRUARY$0.00$04
MARCH$178.00$2245
APRIL$34.00$444

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

sophab

New Member
Joined
May 21, 2019
Messages
3
Welcome to Mr Excel forum

Maybe...
='BOH $'/'Sales $'*'Weeks in Month'

M.
Unfortunately when I do this it divides by the sum of that column. So it ends up dividing by a number larger than 4 or 5. I need to just divide by 4 or 5 depending on which month it's aggregating.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
I'm not sure if I understand what you want.
Is not this?


Month​
Sales $​
BOH $​
Weeks in Month​
Sum of BOHxWeeks​
APRIL​
34,00​
44​
4​
5,18​
442,00​
1492​
4​
13,50​
13981,00​
20231​
4​
5,79​
AUGUST​
2,00​
-24​
4​
-48,00​
23200,00​
19904​
4​
3,43​
DECEMBER​
1776,00​
1838​
5​
5,17​
52669,00​
46723​
5​
4,44​
FEBRUARY​
0,00​
0​
4​
0,00​
632,00​
8​
4​
0,05​
18743,00​
17972​
4​
3,84​
JANUARY​
84,00​
90​
4​
4,29​
13443,00​
4950​
4​
1,47​
MARCH​
178,00​
224​
5​
6,29​
1118,00​
2840​
5​
12,70​
21630,00​
19725​
5​
4,56​
NOVEMBER​
685,00​
18​
4​
0,11​
21810,00​
27126​
4​
4,97​
OCTOBER​
114,00​
131​
4​
4,60​
17217,00​
24933​
4​
5,79​
SEPTEMBER​
1049,00​
1196​
5​
5,70​
23420,00​
19886​
5​
4,25​

<tbody>
</tbody>


Row labels
Month
Sales
BOH
Weeks in Month

M.
 
Last edited:

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
Try putting parentheses around the dollar amounts and adding IFERROR:
Code:
[/COLOR][COLOR=#333333]=IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)[/COLOR][COLOR=#333333]
 

sophab

New Member
Joined
May 21, 2019
Messages
3
I tried with the iferror formula. The issue is the pivot table is summing the # of weeks column so I need a work around. Shown a different way, the right most column is the result I'm looking for. Using January as an example this formula is =BOH $/Sales $ *4. The January total is 1.5=$5,040/$13,527*4. For March however I would need to use =BOH $/Sales $ *5. Or 5.0=$22,789/$22,926*5.

MonthSales $BOH $Average of Weeks In Monthiferror FormulaDESIRED RESULT
JANUARY$13,443$4,950478.11.5
JANUARY$84$90412.94.3
JANUARY Total$13,527$5,040483.51.5
FEBRUARY$18,743$17,9724172.63.8
FEBRUARY$632$840.20.1
FEBRUARY Total$19,375$17,9804181.93.7
MARCH$21,630$19,7255209.74.6
MARCH$1,118$2,840512.712.7
MARCH$178$22456.36.3
MARCH Total$22,926$22,7895238.65.0
APRIL$13,981$20,2314283.65.8
APRIL$442$1,492413.513.5
APRIL$34$4445.25.2
APRIL Total$14,457$21,7674307.16.0
AUGUST$23,200$19,9044298.63.4
AUGUST$2-$244-240.0-48.0
AUGUST Total$23,202$19,8804315.33.4
SEPTEMBER$23,420$19,8865259.04.2
SEPTEMBER$1,049$1,196511.45.7
SEPTEMBER Total$24,469$21,0825271.44.3
OCTOBER$17,217$24,9334318.65.8
OCTOBER$114$13144.64.6
OCTOBER Total$17,331$25,0644323.95.8
NOVEMBER$21,810$27,1264328.35.0
NOVEMBER$685$1840.30.1
NOVEMBER Total$22,495$27,1444333.04.8
DECEMBER$52,669$46,7235275.04.4
DECEMBER$1,776$1,838515.55.2
DECEMBER Total$54,445$48,5615289.94.5

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,089,766
Messages
5,410,296
Members
403,309
Latest member
chaithra

This Week's Hot Topics

Top