# nested IF in calculated field in pivot table

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

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

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