Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table formulas based on text fields
I am trying to advise a client on a formula needed in a pivot table. The
data is downloaded from an external source. the fields in question include the student name, the comp training requirement and the date completed. If not completed, there is no date or any other value in the field. The only way the system shows completion of a comp is by the display of the date. The client would like to show for each student the percent complete of all comps. You can of course, count the number of comps and count the completion dates, but if you try to create a pivot table formula to divide the count of completion date by the count of comps you get a div# error. I have suggested that new fields must be added to the data source to show a number for each records comp and each completed date. This is time consuming as they must download and analyze the data like this several times each week. I could create a macro for them that they could run to do the work for them, but I thought someone would have a better suggestion. Can we create a pivot table formula that converts the text and date values to numbers (like "1") and then do the division? Appreciate any suggestions. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table formulas based on text fields
You can't convert the text to numbers within the pivot table. You could
add a column with formulas to the right of the pivot table, to divide the count of dates by the count of courses. With a bit of programming, you could update the formula column after a download, to ensure that the formulas extend to the bottom of the pivot table. Kay wrote: I am trying to advise a client on a formula needed in a pivot table. The data is downloaded from an external source. the fields in question include the student name, the comp training requirement and the date completed. If not completed, there is no date or any other value in the field. The only way the system shows completion of a comp is by the display of the date. The client would like to show for each student the percent complete of all comps. You can of course, count the number of comps and count the completion dates, but if you try to create a pivot table formula to divide the count of completion date by the count of comps you get a div# error. I have suggested that new fields must be added to the data source to show a number for each records comp and each completed date. This is time consuming as they must download and analyze the data like this several times each week. I could create a macro for them that they could run to do the work for them, but I thought someone would have a better suggestion. Can we create a pivot table formula that converts the text and date values to numbers (like "1") and then do the division? Appreciate any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table formulas based on text fields
Debra,
Thank you! YOu have confrimed what I advised the client in the first place. I think a procedure that they can run each time they download would be the best option. Thanks! "Debra Dalgleish" wrote: You can't convert the text to numbers within the pivot table. You could add a column with formulas to the right of the pivot table, to divide the count of dates by the count of courses. With a bit of programming, you could update the formula column after a download, to ensure that the formulas extend to the bottom of the pivot table. Kay wrote: I am trying to advise a client on a formula needed in a pivot table. The data is downloaded from an external source. the fields in question include the student name, the comp training requirement and the date completed. If not completed, there is no date or any other value in the field. The only way the system shows completion of a comp is by the display of the date. The client would like to show for each student the percent complete of all comps. You can of course, count the number of comps and count the completion dates, but if you try to create a pivot table formula to divide the count of completion date by the count of comps you get a div# error. I have suggested that new fields must be added to the data source to show a number for each records comp and each completed date. This is time consuming as they must download and analyze the data like this several times each week. I could create a macro for them that they could run to do the work for them, but I thought someone would have a better suggestion. Can we create a pivot table formula that converts the text and date values to numbers (like "1") and then do the division? Appreciate any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table formulas based on text fields
Add two columns to the source data:
=--(date0) =1 and divide them in a Calculated Field. Too time consuming? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Usings Text fields in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table fields | Excel Discussion (Misc queries) | |||
number of fields in the row fields in pivot table | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table Based on Two Counted Fields | Excel Discussion (Misc queries) | |||
Pivot Table Fields | Excel Worksheet Functions |