Average Calculation On Text Field 2

[nerdlinger]

I need to get an average on a field within a group. The field I need to average is a character type and the values that it can contain are the numbers 1-10 and the text "Don't Know" and "Refused To Answer" I need to weed out the text strings and get a sum of all the remaining in the group so I can divide that by the totalcount for the group...help

Thanks

 

[kenhamady]

Use the val() function in a formula to convert the text to a number (the text will return 0). Sum this formula.

Now create a second formula that says:

if the Val() formula is > 0
then 1
else 0


Now sum this formula as well. This sum will equal the "count" of records with a response. Now divide the first sum by the second sum (which gives the count) and you will get your average. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[pscad]

Okay, I feel stupid, but I'm trying to do the same thing Nerdlinger is, and I am trying to implement what Ken has suggested, and I'm lost as to how to Sum a formula field. Sorry if you've already covered this, but this is the closest converstation I've come to what I want to do that I've been able to find.

TIA

 

[kenhamady]

It is very easy to Sum a formula field, as long as the formula is eligible for summing. Put the formula on the detail band and then go the insert menu and use Insert - Grand Total/Subtotal. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[pscad]

Thanks Ken. Apparently I have a formula that is not eligible for summing, but I will put this info to good use anyway!

 

[kenhamady]

If you want to know why it is ineligible, post the formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[pscad]

I have 2, neither of which is working. The formulas are: Val ('{TICKETACTIVITY.UNITS}')
and
if Val('{@UnitValue}') > 0
then 1
else 0

Thanks.

 

[kenhamady]

Why do you have the field names in quotes inside the parens?
Remove the quotes and things might work. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[pscad]

Because it's looking for a string? I don't know. I'll just try something else.

Thanks.

 

[kenhamady]

try:

if {TICKETACTIVITY.UNITS} > 0
then 1
else 0 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com