Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jack
 
Posts: n/a
Default 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*R ate,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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Iskus23
 
Posts: n/a
Default 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*R ate,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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jack
 
Posts: n/a
Default nested IF in calculated field in pivot table

Can y send atachments in a post? I thought that was not allowed
Jack
"Iskus23" wrote in message
...
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*R ate,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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculated fields in pivot tables Nigel Drinkwater Excel Worksheet Functions 5 January 3rd 06 06:41 PM
calculated field in a pivot table? baabaa Excel Discussion (Misc queries) 1 November 25th 05 09:08 AM
Pivot Table Calculated Field (Grand total question) Linda Excel Discussion (Misc queries) 5 August 3rd 05 10:11 PM
Using a MIN, MAX formula on a calculated field in a pivot table fhaberland Excel Discussion (Misc queries) 0 August 2nd 05 07:47 PM
Pivot table page field switch to (all) if my criteria is not avail Angus Excel Discussion (Misc queries) 0 July 30th 05 05:06 AM


All times are GMT +1. The time now is 04:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"