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>
 

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,308
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>
 

Forum statistics

Threads
1,078,444
Messages
5,340,322
Members
399,368
Latest member
Sataman

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top