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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Welcome to Mr Excel forum

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

M.
 

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,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

ADVERTISEMENT

Try putting parentheses around the dollar amounts and adding IFERROR:
Code:
[/COLOR][COLOR=#333333]=IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)[/COLOR][COLOR=#333333]
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try putting parentheses around the dollar amounts and adding IFERROR:
Code:
[COLOR=#333333]=IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)[/COLOR]

IFERROR is Ok
Parentheses are not required

M.
 

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,127,581
Messages
5,625,613
Members
416,124
Latest member
DeMoNloK

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
Top