Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search function in a Pivot Table
How would I write the function in an Inserted Calculated field to a Pivot
Report to search Field1 for "Prospects", if present, insert amount from Field2, if not, return 0? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search function in a Pivot Table
All text has the value of zero, so you can't test for specific text in a
calculated field. Maybe you could add a field in the source data, and use a formula to test Field1. Then, sum this new field in the pivot table. HL Questions wrote: How would I write the function in an Inserted Calculated field to a Pivot Report to search Field1 for "Prospects", if present, insert amount from Field2, if not, return 0? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search function in a Pivot Table
Thanks. I've added the columns needed in the source data. My pivot table
reports an employee's time in total and by client, prospecting, admin. Each employee is summed to an office and the offices sum to a department. Columns & Rows as follows Dept Office Empl Client Total Time Client Prospect Admin CS CM John AAA 40 40 CS CM John BBB 30 30 CS CM John CCC 20 20 CS CM Jane AAA 80 80 CS SF Tom CCC 50 50 CS SF Tina DDD 60 60 I would like to create a calculated field for each of client time, prospect, admin as a percentage of the Employees total time. Then, for the office, the sum of each of client time, prospect, admin as a percentage of all the Office's Employees' Total Time. Then, for the Dept, the sum of each of client time, prospect, admin as a percentage of all the Dept's Employees' Total time. I can am only able to write the formula for: Prospect/Total Time for the calculated field. Which always returns 100% for each of the above categories. What formula could be used? "Debra Dalgleish" wrote: All text has the value of zero, so you can't test for specific text in a calculated field. Maybe you could add a field in the source data, and use a formula to test Field1. Then, sum this new field in the pivot table. HL Questions wrote: How would I write the function in an Inserted Calculated field to a Pivot Report to search Field1 for "Prospects", if present, insert amount from Field2, if not, return 0? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find function Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Function | Excel Worksheet Functions | |||
Sort function on Pivot Table | Excel Worksheet Functions | |||
Pivot Table or Function?? | Excel Discussion (Misc queries) | |||
subtotal - pivot table - or better function | Excel Worksheet Functions |