+ Reply to Thread
Results 1 to 3 of 3

nested IF in calculated field in pivot table

  1. #1
    Jack
    Guest

    nested IF in calculated field in pivot table

    I have data that contains 2 columns: "terms" and "rate". Terms is either
    "monthly" or "weekly". I want to create a formula for a pivot table that
    computes the Annual amount.
    My formula is as follows:
    =IF(terms="monthly",12*Rate,IF(terms="weekly",52*Rate,0))
    The result is always 0. I have trimmed my terms column but to no
    vail..
    If I create a formula:

    =IF(terms="monthly",12*Rate,52*Rate)

    This works. It s not what I want since eventually the terms column will
    inlcude quarterly and bi-monthly options which will require a nested IF
    If I use the IIF function, I get an error and it wont let me enter that
    function.
    I am not sure what I am doing wrong.
    Jack



  2. #2
    Iskus23
    Guest

    RE: nested IF in calculated field in pivot table

    Jack,

    Can you send your file with the nested IF statements. If I look at your
    formula, perhaps I can figure out your problem. I've used nested IF
    statements. I think 7 or 8 is the max allowed, but there are ways around
    that if you need to.

    "Jack" wrote:

    > I have data that contains 2 columns: "terms" and "rate". Terms is either
    > "monthly" or "weekly". I want to create a formula for a pivot table that
    > computes the Annual amount.
    > My formula is as follows:
    > =IF(terms="monthly",12*Rate,IF(terms="weekly",52*Rate,0))
    > The result is always 0. I have trimmed my terms column but to no
    > vail..
    > If I create a formula:
    >
    > =IF(terms="monthly",12*Rate,52*Rate)
    >
    > This works. It s not what I want since eventually the terms column will
    > inlcude quarterly and bi-monthly options which will require a nested IF
    > If I use the IIF function, I get an error and it wont let me enter that
    > function.
    > I am not sure what I am doing wrong.
    > Jack
    >
    >
    >


  3. #3
    Jack
    Guest

    Re: nested IF in calculated field in pivot table

    Can y send atachments in a post? I thought that was not allowed
    Jack
    "Iskus23" <Iskus23@discussions.microsoft.com> wrote in message
    news:AE3ED45E-776C-4D19-A19E-C34AE9AB4A55@microsoft.com...
    > Jack,
    >
    > Can you send your file with the nested IF statements. If I look at your
    > formula, perhaps I can figure out your problem. I've used nested IF
    > statements. I think 7 or 8 is the max allowed, but there are ways around
    > that if you need to.
    >
    > "Jack" wrote:
    >
    >> I have data that contains 2 columns: "terms" and "rate". Terms is either
    >> "monthly" or "weekly". I want to create a formula for a pivot table that
    >> computes the Annual amount.
    >> My formula is as follows:
    >> =IF(terms="monthly",12*Rate,IF(terms="weekly",52*Rate,0))
    >> The result is always 0. I have trimmed my terms column but to no
    >> vail..
    >> If I create a formula:
    >>
    >> =IF(terms="monthly",12*Rate,52*Rate)
    >>
    >> This works. It s not what I want since eventually the terms column will
    >> inlcude quarterly and bi-monthly options which will require a nested IF
    >> If I use the IIF function, I get an error and it wont let me enter that
    >> function.
    >> I am not sure what I am doing wrong.
    >> Jack
    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1