Results 1 to 7 of 7

Thread: Calculated Field in Pivot Tables
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculated Field in Pivot Tables

    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

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,273
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Calculated Field in Pivot Tables

    Welcome to Mr Excel forum

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

    M.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculated Field in Pivot Tables

    Quote Originally Posted by Marcelo Branco View Post
    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.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,273
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Calculated Field in Pivot Tables

    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


    Row labels
    Month
    Sales
    BOH
    Weeks in Month

    M.
    Last edited by Marcelo Branco; May 21st, 2019 at 02:26 PM.

  5. #5
    Board Regular
    Join Date
    Apr 2015
    Posts
    184
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculated Field in Pivot Tables

    Try putting parentheses around the dollar amounts and adding IFERROR:
    Code:
    =IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,273
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Calculated Field in Pivot Tables

    Quote Originally Posted by GR00007 View Post
    Try putting parentheses around the dollar amounts and adding IFERROR:
    Code:
    =IFERROR(('BOH $'/'Sales $')*'Weeks in Month',0)
    IFERROR is Ok
    Parentheses are not required

    M.

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculated Field in Pivot Tables

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •