Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated fields in pivot tables | Excel Worksheet Functions | |||
calculated field in a pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Field (Grand total question) | Excel Discussion (Misc queries) | |||
Using a MIN, MAX formula on a calculated field in a pivot table | Excel Discussion (Misc queries) | |||
Pivot table page field switch to (all) if my criteria is not avail | Excel Discussion (Misc queries) |