Calculated Field in Pivot Tables

[sophab]

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!

[TABLE="class: grid, width: 380, align: left"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Sales $[/TD]
[TD]BOH $[/TD]
[TD]Weeks in Month[/TD]
[/TR]
[TR]
[TD]JANUARY[/TD]
[TD="align: right"]$13,443.00[/TD]
[TD="align: right"]$4,950[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]FEBRUARY[/TD]
[TD="align: right"]$18,743.00[/TD]
[TD="align: right"]$17,972[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]MARCH[/TD]
[TD="align: right"]$21,630.00[/TD]
[TD="align: right"]$19,725[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]APRIL[/TD]
[TD="align: right"]$13,981.00[/TD]
[TD="align: right"]$20,231[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]AUGUST[/TD]
[TD="align: right"]$23,200.00[/TD]
[TD="align: right"]$19,904[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]SEPTEMBER[/TD]
[TD="align: right"]$23,420.00[/TD]
[TD="align: right"]$19,886[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD="align: right"]$17,217.00[/TD]
[TD="align: right"]$24,933[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]NOVEMBER[/TD]
[TD="align: right"]$21,810.00[/TD]
[TD="align: right"]$27,126[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]DECEMBER[/TD]
[TD="align: right"]$52,669.00[/TD]
[TD="align: right"]$46,723[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]JANUARY[/TD]
[TD="align: right"]$84.00[/TD]
[TD="align: right"]$90[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]FEBRUARY[/TD]
[TD="align: right"]$632.00[/TD]
[TD="align: right"]$8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]MARCH[/TD]
[TD="align: right"]$1,118.00[/TD]
[TD="align: right"]$2,840[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]APRIL[/TD]
[TD="align: right"]$442.00[/TD]
[TD="align: right"]$1,492[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]AUGUST[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]-$24[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]SEPTEMBER[/TD]
[TD="align: right"]$1,049.00[/TD]
[TD="align: right"]$1,196[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD="align: right"]$114.00[/TD]
[TD="align: right"]$131[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]NOVEMBER[/TD]
[TD="align: right"]$685.00[/TD]
[TD="align: right"]$18[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]DECEMBER[/TD]
[TD="align: right"]$1,776.00[/TD]
[TD="align: right"]$1,838[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]FEBRUARY[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]MARCH[/TD]
[TD="align: right"]$178.00[/TD]
[TD="align: right"]$224[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]APRIL[/TD]
[TD="align: right"]$34.00[/TD]
[TD="align: right"]$44[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

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

M.

 

[sophab]

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]

I'm not sure if I understand what you want.
Is not this?


[TABLE="class: grid"]
<tbody>[TR]
[TD]

Month​

[/TD]
[TD]

Sales $​

[/TD]
[TD]

BOH $​

[/TD]
[TD]

Weeks in Month​

[/TD]
[TD]

Sum of BOHxWeeks​

[/TD]
[/TR]
[TR]
[TD]

APRIL​

[/TD]
[TD]

34,00​

[/TD]
[TD]

44​

[/TD]
[TD]

4​

[/TD]
[TD]

5,18​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

442,00​

[/TD]
[TD]

1492​

[/TD]
[TD]

4​

[/TD]
[TD]

13,50​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

13981,00​

[/TD]
[TD]

20231​

[/TD]
[TD]

4​

[/TD]
[TD]

5,79​

[/TD]
[/TR]
[TR]
[TD]

AUGUST​

[/TD]
[TD]

2,00​

[/TD]
[TD]

-24​

[/TD]
[TD]

4​

[/TD]
[TD]

-48,00​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

23200,00​

[/TD]
[TD]

19904​

[/TD]
[TD]

4​

[/TD]
[TD]

3,43​

[/TD]
[/TR]
[TR]
[TD]

DECEMBER​

[/TD]
[TD]

1776,00​

[/TD]
[TD]

1838​

[/TD]
[TD]

5​

[/TD]
[TD]

5,17​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

52669,00​

[/TD]
[TD]

46723​

[/TD]
[TD]

5​

[/TD]
[TD]

4,44​

[/TD]
[/TR]
[TR]
[TD]

FEBRUARY​

[/TD]
[TD]

0,00​

[/TD]
[TD]

0​

[/TD]
[TD]

4​

[/TD]
[TD]

0,00​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

632,00​

[/TD]
[TD]

8​

[/TD]
[TD]

4​

[/TD]
[TD]

0,05​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

18743,00​

[/TD]
[TD]

17972​

[/TD]
[TD]

4​

[/TD]
[TD]

3,84​

[/TD]
[/TR]
[TR]
[TD]

JANUARY​

[/TD]
[TD]

84,00​

[/TD]
[TD]

90​

[/TD]
[TD]

4​

[/TD]
[TD]

4,29​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

13443,00​

[/TD]
[TD]

4950​

[/TD]
[TD]

4​

[/TD]
[TD]

1,47​

[/TD]
[/TR]
[TR]
[TD]

MARCH​

[/TD]
[TD]

178,00​

[/TD]
[TD]

224​

[/TD]
[TD]

5​

[/TD]
[TD]

6,29​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

1118,00​

[/TD]
[TD]

2840​

[/TD]
[TD]

5​

[/TD]
[TD]

12,70​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

21630,00​

[/TD]
[TD]

19725​

[/TD]
[TD]

5​

[/TD]
[TD]

4,56​

[/TD]
[/TR]
[TR]
[TD]

NOVEMBER​

[/TD]
[TD]

685,00​

[/TD]
[TD]

18​

[/TD]
[TD]

4​

[/TD]
[TD]

0,11​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

21810,00​

[/TD]
[TD]

27126​

[/TD]
[TD]

4​

[/TD]
[TD]

4,97​

[/TD]
[/TR]
[TR]
[TD]

OCTOBER​

[/TD]
[TD]

114,00​

[/TD]
[TD]

131​

[/TD]
[TD]

4​

[/TD]
[TD]

4,60​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

17217,00​

[/TD]
[TD]

24933​

[/TD]
[TD]

4​

[/TD]
[TD]

5,79​

[/TD]
[/TR]
[TR]
[TD]

SEPTEMBER​

[/TD]
[TD]

1049,00​

[/TD]
[TD]

1196​

[/TD]
[TD]

5​

[/TD]
[TD]

5,70​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

23420,00​

[/TD]
[TD]

19886​

[/TD]
[TD]

5​

[/TD]
[TD]

4,25​

[/TD]
[/TR]
</tbody>[/TABLE]


Row labels
Month
Sales
BOH
Weeks in Month

M.

 

[GR00007]

Try putting parentheses around the dollar amounts and adding IFERROR:

[/COLOR][COLOR=#333333]=IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)[/COLOR][COLOR=#333333]

 

[Marcelo Branco]

Try putting parentheses around the dollar amounts and adding IFERROR:

[COLOR=#333333]=IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)[/COLOR]

IFERROR is Ok
Parentheses are not required

M.

 

[sophab]

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.

[TABLE="class: grid, width: 653"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Sales $[/TD]
[TD]BOH $[/TD]
[TD]Average of Weeks In Month[/TD]
[TD]iferror Formula[/TD]
[TD]DESIRED RESULT[/TD]
[/TR]
[TR]
[TD]JANUARY[/TD]
[TD="align: right"]$13,443[/TD]
[TD="align: right"]$4,950[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]78.1[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD]JANUARY[/TD]
[TD="align: right"]$84[/TD]
[TD="align: right"]$90[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12.9[/TD]
[TD="align: right"]4.3[/TD]
[/TR]
[TR]
[TD]JANUARY Total[/TD]
[TD="align: right"]$13,527[/TD]
[TD="align: right"]$5,040[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]83.5[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD]FEBRUARY[/TD]
[TD="align: right"]$18,743[/TD]
[TD="align: right"]$17,972[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]172.6[/TD]
[TD="align: right"]3.8[/TD]
[/TR]
[TR]
[TD]FEBRUARY[/TD]
[TD="align: right"]$632[/TD]
[TD="align: right"]$8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD]FEBRUARY Total[/TD]
[TD="align: right"]$19,375[/TD]
[TD="align: right"]$17,980[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]181.9[/TD]
[TD="align: right"]3.7[/TD]
[/TR]
[TR]
[TD]MARCH[/TD]
[TD="align: right"]$21,630[/TD]
[TD="align: right"]$19,725[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]209.7[/TD]
[TD="align: right"]4.6[/TD]
[/TR]
[TR]
[TD]MARCH[/TD]
[TD="align: right"]$1,118[/TD]
[TD="align: right"]$2,840[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12.7[/TD]
[TD="align: right"]12.7[/TD]
[/TR]
[TR]
[TD]MARCH[/TD]
[TD="align: right"]$178[/TD]
[TD="align: right"]$224[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD]MARCH Total[/TD]
[TD="align: right"]$22,926[/TD]
[TD="align: right"]$22,789[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]238.6[/TD]
[TD="align: right"]5.0[/TD]
[/TR]
[TR]
[TD]APRIL[/TD]
[TD="align: right"]$13,981[/TD]
[TD="align: right"]$20,231[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]283.6[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
[TR]
[TD]APRIL[/TD]
[TD="align: right"]$442[/TD]
[TD="align: right"]$1,492[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13.5[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD]APRIL[/TD]
[TD="align: right"]$34[/TD]
[TD="align: right"]$44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5.2[/TD]
[TD="align: right"]5.2[/TD]
[/TR]
[TR]
[TD]APRIL Total[/TD]
[TD="align: right"]$14,457[/TD]
[TD="align: right"]$21,767[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]307.1[/TD]
[TD="align: right"]6.0[/TD]
[/TR]
[TR]
[TD]AUGUST[/TD]
[TD="align: right"]$23,200[/TD]
[TD="align: right"]$19,904[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]298.6[/TD]
[TD="align: right"]3.4[/TD]
[/TR]
[TR]
[TD]AUGUST[/TD]
[TD="align: right"]$2[/TD]
[TD="align: right"]-$24[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-240.0[/TD]
[TD="align: right"]-48.0[/TD]
[/TR]
[TR]
[TD]AUGUST Total[/TD]
[TD="align: right"]$23,202[/TD]
[TD="align: right"]$19,880[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]315.3[/TD]
[TD="align: right"]3.4[/TD]
[/TR]
[TR]
[TD]SEPTEMBER[/TD]
[TD="align: right"]$23,420[/TD]
[TD="align: right"]$19,886[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]259.0[/TD]
[TD="align: right"]4.2[/TD]
[/TR]
[TR]
[TD]SEPTEMBER[/TD]
[TD="align: right"]$1,049[/TD]
[TD="align: right"]$1,196[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11.4[/TD]
[TD="align: right"]5.7[/TD]
[/TR]
[TR]
[TD]SEPTEMBER Total[/TD]
[TD="align: right"]$24,469[/TD]
[TD="align: right"]$21,082[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]271.4[/TD]
[TD="align: right"]4.3[/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD="align: right"]$17,217[/TD]
[TD="align: right"]$24,933[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]318.6[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
[TR]
[TD]OCTOBER[/TD]
[TD="align: right"]$114[/TD]
[TD="align: right"]$131[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.6[/TD]
[/TR]
[TR]
[TD]OCTOBER Total[/TD]
[TD="align: right"]$17,331[/TD]
[TD="align: right"]$25,064[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]323.9[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
[TR]
[TD]NOVEMBER[/TD]
[TD="align: right"]$21,810[/TD]
[TD="align: right"]$27,126[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]328.3[/TD]
[TD="align: right"]5.0[/TD]
[/TR]
[TR]
[TD]NOVEMBER[/TD]
[TD="align: right"]$685[/TD]
[TD="align: right"]$18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.1[/TD]
[/TR]
[TR]
[TD]NOVEMBER Total[/TD]
[TD="align: right"]$22,495[/TD]
[TD="align: right"]$27,144[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]333.0[/TD]
[TD="align: right"]4.8[/TD]
[/TR]
[TR]
[TD]DECEMBER[/TD]
[TD="align: right"]$52,669[/TD]
[TD="align: right"]$46,723[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]275.0[/TD]
[TD="align: right"]4.4[/TD]
[/TR]
[TR]
[TD]DECEMBER[/TD]
[TD="align: right"]$1,776[/TD]
[TD="align: right"]$1,838[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]5.2[/TD]
[/TR]
[TR]
[TD]DECEMBER Total[/TD]
[TD="align: right"]$54,445[/TD]
[TD="align: right"]$48,561[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]289.9[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
</tbody>[/TABLE]