Pivot table formulas based on text fields

[Kay]

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]

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]

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!

 

[Herbert Seidenberg]

Add two columns to the source data:
=--(date>0)
=1
and divide them in a Calculated Field.
Too time consuming?