5 Replies Latest reply on Oct 19, 2010 8:51 AM by etripoli

# Working out the mean of a set number of records

### Title

Working out the mean of a set number of records

### Post

Iam trying to work out the mean of a set number of related records say the last 5 records. Iam just wondering whats the best way to get the data from the last five records to work the mean out?

Ive have the data displayed in a portal

Thanks Jon

• ###### 1. Re: Working out the mean of a set number of records

You have two options you can explore: Average ( RelatedTable::Datafield ) and a summary field defined in the portal table that computes the "Average of" this same field.

• ###### 2. Re: Working out the mean of a set number of records

Try using Average ( RightValues ( List ( Related Table::Field ), 5 ) ) to get the average of the last 5 values, and place it in a calculation field in the parent table.

• ###### 3. Re: Working out the mean of a set number of records

Thanks guys managed to figure it average bit out

now I have the mean of the data in a field iam looking to somehow count them for instants is the data is > the mean 9 consecutive times a e-mail will be sent same if is < the mean 9 times in a row

what would be the best way to achive this? have two files BelowMean and AdoveMean and in a script something like

IF DataField > AverageField

SetField BelowMean 0

Else IF DataField < AverageField

+1 BelowMean

Are would you recommend another way?

• ###### 4. Re: Working out the mean of a set number of records

I think you need to explain what you are trying to do in more detail. "> the mean 9 consecutive times" is that a newly calculated mean each time or the original mean? If it's recalculated each time, what are the data sets used each time?

I'm guessing you may need to set up a relationship for this to keep track of the mean for each set of related records, but could easily be wrong here.

Giving an example of how you want this to work would help fill in some of the blanks.

• ###### 5. Re: Working out the mean of a set number of records

Seems like you're on the right track.  However, besides checking if the value of the field current record is above or below the mean, you also need to keep track of the previous value, i.e. whether the values are consecutive, and the number of times the value has been consecutive.  Tracking these using variables would be better than creating fields.  And, if the values are not consecutive, then all the variables need to be reset.