Skip to Content
avatar image
Former Member

How do I get an average for each row

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 ?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 14, 2016 at 06:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 14, 2016 at 06:01 PM

    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

    Add comment
    10|10000 characters needed characters exceeded