Complex - Average 4 previous rows, divide, multiple, subtract, and round

[TE21]

This problem is not for the beginner that I am. I have invested a lot of time into Appsheet and I’m too stubborn to give up. I am hoping someone can assist me with this very complex issue (at least complex to me). After trying for two days, it’s time to suck up my pride and ask for help.

I have a form that allows people to login and log out equipment. There are multiple devices. Each device is identified by a serial number. This equipment has to be calibrated every day. I have a form where the user will input a four digit number into Daily[Data] each day the equipment is used. I need to know the percent difference of that “new” number based on the average of the previous four entered.

Basically, I need to be able to average (and roundup to the nearest 1/10 of a percent) the previous 4 rows of Daily[Data], then divide it by the current value entered for that day. From there, I need to display in a column labeled Daily[Avg] the percent difference between the 4 previous numbers and the current number.

Some devices are used daily, others are not. That is where my main issue lies. I need this to “scan” the column Daily[Serial] to find the previous four Daily[Data] numbers that are related to that particular device so it can average them and determine the percentage of difference when compared to “today’s” new number.

For instance:
Here is an example layout of the data that gets captured from the form.

If the data being entered “today” goes into Row 9, the equipment number is 012, and the value being entered into Daily[Data] is equal to 3584. I need to determine the AVG column.

The formula will need to average and round the previous 4 rows, divide by 3584, multiple by 100 and finally subtract 100 from that value and display that value as a percentage of difference into the column labeled Daily[Avg], highlighted in Yellow in the image above. To understand this further please see my step by step plan below.

Row | Serial | Data

Row 2 | 012 | 1234

Row 4 | 012 | 6987

Row 6 | 012 | 3211

Row 8 | 012 | 7899

Avg of Row 2, 4, 6 and 8 = 4833
Divide 3584 into 4833 = 0.742
0.74 * 100 = 74.2
100 - 74.2 = 25.8

Therefore the difference is 25.8%

The value of 25.8% is what I need displayed in Daily[Avg]

I am sorry for the lengthy post but I figure it’s best to be as detailed as possible. I appreciate any help you guys and/or gals can give me! Thanks again!

[Marc_Dillon · Accepted solution]

I would add a Real column to the Daily Table, which would hold a List of key values of the previous 4 records. Use the following expression as an Initial Value, which gets all records with the same [Serial], orders them descending by [_RowNumber], then takes the first 4 (i.e. the last 4).

TOP(
  ORDERBY( 
    FILTER(
      Daily ,
      [Serial] = [_THISROW].[Serial]
    ) ,
    [_RowNumber] , TRUE 
  ) ,
  4
)

Set this column as an EnumList with Base Type as Ref, pointing to the Daily Table.

Then you can get the average with:

AVERAGE( [New Column][Data] )

I assume that was the part you couldn’t figure out and you can handle the rest of the calculation from there.

[TE21]

Thank you @Marc_Dillon!!! Sorry for the late reply, but just getting back. This worked!