# 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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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