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!

Month	Sales $	BOH $	Weeks in Month
JANUARY	$13,443.00	$4,950	4
FEBRUARY	$18,743.00	$17,972	4
MARCH	$21,630.00	$19,725	5
APRIL	$13,981.00	$20,231	4
AUGUST	$23,200.00	$19,904	4
SEPTEMBER	$23,420.00	$19,886	5
OCTOBER	$17,217.00	$24,933	4
NOVEMBER	$21,810.00	$27,126	4
DECEMBER	$52,669.00	$46,723	5
JANUARY	$84.00	$90	4
FEBRUARY	$632.00	$8	4
MARCH	$1,118.00	$2,840	5
APRIL	$442.00	$1,492	4
AUGUST	$2.00	-$24	4
SEPTEMBER	$1,049.00	$1,196	5
OCTOBER	$114.00	$131	4
NOVEMBER	$685.00	$18	4
DECEMBER	$1,776.00	$1,838	5
FEBRUARY	$0.00	$0	4
MARCH	$178.00	$224	5
APRIL	$34.00	$44	4

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

 

[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?

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.

 

[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.

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

<tbody>
</tbody>