Calculated Field in Pivot Tables

sophab

New Member
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>

Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Marcelo Branco

MrExcel MVP
Welcome to Mr Excel forum

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

M.

sophab

New Member
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
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

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

Replies
21
Views
471
Replies
6
Views
154
Replies
2
Views
64
Replies
3
Views
132
Replies
0
Views
77

1,127,260
Messages
5,623,687
Members
415,983
Latest member
MusicMan

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.

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

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