How do I get an average for each row

[Former Member]

Ok so I think I have a simple report.  I am displaying  sensory or quality data ( like taste, color, etc.) by each product made on a certain day.  All tests have a integer value ( 0 -10 ).  So my details section looks similiar to the below example.... what i want to do is get the average of each row, in the details section.......

Please help....

thanks

doug

productName    date   evaluator     color   taste  smell sweetness  texture            rowAVG

  prodA             4/11/16    doug       5           4      3       4                4                          ?

prodB             4/11/16    doug       5           4      3       6                   7                      ?

[Former Member · Accepted solution]

My apologies.  I have resolved my own issue.  On my formula I had "Exceptions for Nulls" selected instead of "Default values for Nulls".  Since I am comparing the data fields to '0' in my formula this caused it to not work.  For those interested in getting an average for rows in CR; below is a formula I am using.

if {procGetSensoryTestResultsFGRpt;1.aFruitColor} <> 0

then

(

   numberVar totAvgSum := {procGetSensoryTestResultsFGRpt;1.aFruitColor} ;

   numberVar totAvgCount := 1 ;

)

else

(

  totAvgSum := 0;

  totAvgCount := 0;

);

if  ({procGetSensoryTestResultsFGRpt;1.aLiqAmount}) <> 0

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.aLiqAmount}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

if  ({procGetSensoryTestResultsFGRpt;1.aTomColor}) <> 0

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.aTomColor}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

if  ({procGetSensoryTestResultsFGRpt;1.aTomPieceInt}) <> 0

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.aTomPieceInt}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

if  ({procGetSensoryTestResultsFGRpt;1.aVisualViscosity}) <> 0

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.aVisualViscosity}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.fCiliantro}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.fFruitBalance}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum + ({procGetSensoryTestResultsFGRpt;1.fHeat}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.fSalt}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.fSour}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.fSweet}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

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

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.fTomatoBalance}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

if  ({procGetSensoryTestResultsFGRpt;1.pDef}) <> 0

then

(

   numberVar totAvgSum := totAvgSum +  ({procGetSensoryTestResultsFGRpt;1.pDef}) ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

if  ({procGetSensoryTestResultsFGRpt;1.tFruitTexture}) <> 0

then

(

   numberVar totAvgSum := totAvgSum + {procGetSensoryTestResultsFGRpt;1.tFruitTexture} ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

if  ({procGetSensoryTestResultsFGRpt;1.tTomatoTexture}) <> 0

then

(

   numberVar totAvgSum := totAvgSum + {procGetSensoryTestResultsFGRpt;1.tTomatoTexture} ;

   numberVar totAvgCount := totAvgCount + 1 ;

);

totAvgSum / totAvgCount

[DellSC]

This is fairly easy.  You would create a formula like this:

({MyTable.color} + {MyTable.taste} + {MyTable.smell} + {MyTable.sweetness} + {MyTable.texture}) / 5

Use this formula on your report to get the average.

-Dell