7 Replies Latest reply on Dec 2, 2014 11:55 AM by fmpro_novice

# Help with a formula (Revised)

### Title

Help with a formula (Revised)

### Post

I'm trying to find the average of only the last 3 fields (that contain data) for the record showing in this diagram.

The fields are simply named One, Two, Three, Four, etc. I need the average to evaluate only the last 3 numbers entered. In the example shown, the first 6 numbers would be ignored and the average would only be based on the numbers 8, 5 and 6. Hence, the average of 6.3 showing in the gray box.

(The average is only showing correctly because I drew this in another program.)

• ###### 1. Re: Help with a formula (Revised)

Could you post a picture of the layout in Layout Mode?

• ###### 2. Re: Help with a formula (Revised)

Here it is. It's pretty much the same as Browse mode. Where you see the 1, 2, 3, 4....across the top, the field names are called One, Two, Three, Four,.....etc (There are a total of 52, one for each week). In Layout, where the black box says HI Ca, that's the Average field.

• ###### 3. Re: Help with a formula (Revised)

This sounds like something that would be far easier to manage if you were to use a set of related records, one record for each value, instead of a set of identical fields.

• ###### 4. Re: Help with a formula (Revised)

I'm sure you are correct, but the numbers actually originate from another source and are imported. Is there a way to evaluate all the fields, determine which one has the newest entry and go back? These numbers represent scores that are earned on a weekly basis. But it is possible that someone was absent on any given week and did not have a score so the average drops any empty field and just averages whatever the last 3 scores are.

• ###### 5. Re: Help with a formula (Revised)

It would still be possible to import the data and put the imported data into a set of related records as part of the import process.

Will the values always be recorded from left to right?

• ###### 6. Re: Help with a formula (Revised)

Let ( [ x = List ( one ; two ; three ; four ... twenty ) ; // you'll have to enter all the field names here
x3a = RightValues ( x ; 3 ) ;
x3 = Left ( x3a ; Length ( x3a ) - 1 ) ]; // this line strips off an extra ¶

If ( valueCount ( x ) < 3 ; Evaluate ( "min ( " & Substitute ( x ; ¶ ; "; " ) & " )" ) ;
Evaluate ( "Average ( " & Substitute ( x3 ; ¶ ; "; " ) & ")" )
)

)

• ###### 7. Re: Help with a formula (Revised)

Hi Phil - Thank you again for your time, you are very much appreciated on this forum! Your solution was right on track.

Thanks again!