Total Average of non null average fields

[Former Member]

I have a subreport that shows data from a stored procedure.  This data happens to be flavor specs for a product.  I am calculating the average of each flavor spec.  I did that by inserting a summary calculation and changed it to Average.  That is working fine.  What I need now is to calculate the overall average.  The issue is that some of the specs have no values at all, because they were not tested for that product.  For example if i have 5 specs, and 5 averages for each spec, but only 3 of the averages have values because the other two specs had not data, i want my overall average only to include those 3 specs.  How do I write a formula that tells crystal to take the average, but only where is not null?  I have included a screen shot of my actual report.  As you can see I already have averages for each column.  However, some of these column averages could be null in which case they should not be included in the total average

[abhilash_kumar · Accepted solution]

Hi Doug,

Try this please:

1) Create a formula with this code:

If (NOT isNull({field_to_average}) OR {field_to_average} <> 0) then

{field_to_average}

2) Go to the Insert Summary option > Select this formula field as the 'Field to Summarize' > Choose 'Weighted Average'.

-Abhilash

[Former Member · Accepted solution]

Thanks for the quick reply.  Unfortunately it will not allow me to do as you suggest.  Under the 'Field to Summarize' drop down, all that is available are actual fields on my report and not the formula.

thanks

Doug

[abhilash_kumar · Accepted solution]

Could you paste the formula you've created based on what I suggested above?

Does the {field to summarize} refer to another formula field/summary?

-Abhilash

[Former Member · Accepted solution]

Here is what I created based upon your suggestion:

If (NOT isNull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic})) OR Average ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic}) <> 0) then

Average ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic})

Also below is another formula I created previous, trying to generate a total average from the 9 column averages.

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic})) 

then 0

else

numberVar AvgSum := Average ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic}) ;

numberVar AvgCount := 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fCiliantro})) 

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fCiliantro}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fFruitBalance}))

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fFruitBalance}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fGarlic})) 

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fGarlic}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fHeat}))

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fHeat}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fSalt}))

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fSalt}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fSour}))

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fSour}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fSweet}))

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fSweet}) ;

numberVar AvgCount := AvgCount + 1;

if isnull(Average ({procGetSensoryFlavorMonthlyTestResults;1.fTomatoBalance}))

then 0

else

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fTomatoBalance}) ;

numberVar AvgCount := AvgCount + 1;

AvgSum / AvgCount

[abhilash_kumar · Accepted solution]

Change the code to:

If (NOT isNull({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic}) OR {procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic} <> 0) then ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic})

You should now be able to insert a Weighted Average against this formula field.

-Abhilash

[Former Member · Accepted solution]

Thanks but how is this going to get me what I want?  This formula only takes into account 1 field or column.  What I have is 9 Averages already.  I the BasilGarlic avg and 8 others.  I want an average of those 9 averages, but only where the average does not equal NULL.

So For example:

  Avg1 , Avg2, Avg3, Avg4

   totavg = Avg1+avg2+avg3+avg4 / 4  *but only where avg is not null

         so if avg3 happened to return Null or 0 because no readings were taken for that column the formula would be as follow

                totavg = avg1+avg2+avg3+avg4 / 3

[abhilash_kumar · Accepted solution]

OK, I see what you're trying to do now. Try this please:

if Average({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic}) <> 0

Then

(

numberVar AvgSum := Average ({procGetSensoryFlavorMonthlyTestResults;1.fBasilGarlic}) ;

numberVar AvgCount := 1;

);

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fCiliantro}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fCiliantro}) ;

numberVar AvgCount := AvgCount + 1;

);

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fFruitBalance}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fFruitBalance}) ;

numberVar AvgCount := AvgCount + 1;

) ;

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fGarlic}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fGarlic}) ;

numberVar AvgCount := AvgCount + 1;

) ;

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fHeat}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fHeat}) ;

numberVar AvgCount := AvgCount + 1;

) ;

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fSalt}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fSalt}) ;

numberVar AvgCount := AvgCount + 1;

) ;

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fSour}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fSour}) ;

numberVar AvgCount := AvgCount + 1;

) ;

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fSweet}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fSweet}) ;

numberVar AvgCount := AvgCount + 1;

) ;

if Average ({procGetSensoryFlavorMonthlyTestResults;1.fTomatoBalance}) <> 0

then

(

numberVar AvgSum := AvgSum + Average ({procGetSensoryFlavorMonthlyTestResults;1.fTomatoBalance}) ;

numberVar AvgCount := AvgCount + 1;

) ;

AvgSum / AvgCount;

-Abhilash

[Former Member · Accepted solution]

That worked great!!  Thanks for the help and your patience

[Former Member · Accepted solution]

One more question.  Is there a way to declare and set the number variables to 0 initially?  I am wondering what would happen if the first field in the formula, fBasilGarlic, is null.  Will the number variable AvgSum and AvgCount be 0, or some indeterminate value like null or nothing when the second if clause is evaluated?

thanks

doug