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.
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!