Averaging multiple fields 1

[relaxinjim]

I am using Crystal 9.
I am interested in calculating the average of multiple fields 'horizontally' in a report. I set up a formula adding the fields and then dividing by the number of fields:
(fld1 + fld2 + fld3 + fld4)/4
(3 + 2 + 4 + 3)/4 = 3
This returned me the correct answer for most situations.
However, my problem relates to a few instances were a fields have the value of "0". In my report a field with the value of "0" can not count in the average. For example:
(fld1 + fld2 + fld3 + fld4)/4
(3 + 2 + 0 + 3)/4 = 2
This formula does not return the answer I need. I need the formula to "adjust" to potential "0" values to read like this:
(fld1 + fld2 + fld3 + fld4)/3
(3 + 2 + 0 + 3)/3 = 2.66
Any suggestions are appreciated.
Thanks

 

[Madawc]

The simplest way is to replace the divisor by a formula field, and for this formula field to count the number of non-zero fields.

If these were fields in different records or 'rows', you could do it with a running-total count that ignored zero values. If they all come from the same row, then you'll need to long-windedly cover all possible combinations of zero and non-zero. I.e. if fld1 = 0 then if fld2 = 0 etc.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[relaxinjim]

Thank you for the response Madawc. I have not been able to construct the divisor as you indicate.
My situation relates to you second instance "If they all come from the same row, then you'll need to long-windedly cover all possible combinations of zero and non-zero."
This presents a cumbersom task becuase there are 27 records I am averaging in one instance.
Would you be able to elaborate on your idea?

Here is a sample of my data:

ID# Fld1 Fld2 Fld3...Fld25
1 2 1 2 3
3 3 2 2 4
4 0 2 0 4
7 4 0 1 1

Thank you again!

 

[lbass]

You could use the following formula for the denominator in your average:

numbervar array x := [{table.field1},{table.field2},{table.field27}]; //add all 27 fields into this array
numbervar i := 0;
numbervar j := ubound(x);
numbervar cnt := 0;

for i := 1 to j do(
if x > 0 then
cnt := cnt + 1);
cnt

-LB

 

[relaxinjim]

Thank you for your input LB. I inserted your code into the denominator with the appropriate fields and received an error message when I checked the formula:

"This array must be subscripted. For example: Array ."

I don't know anything about arrays so this is greek to me. I did try to putting [] around the "x" located after "array" but this didn't do the trick. I will search for another solution.

If anyone is familiar with this error feel free to comment.

Thanks

 

[lbass]

It is "cnt" that you would use in the denominator. You could set your average formula up like this:

numbervar array x := [{table.field1},{table.field2},{table.field27}]; //add all 27 fields into this array
numbervar i := 0;
numbervar j := ubound(x);
numbervar cnt := 0;

for i := 1 to j do(
if x > 0 then
cnt := cnt + 1);
if cnt > 0 then
sum(x)/cnt

-LB

 

[relaxinjim]

You got it! This code works like a charm. Thank you very much.