Average Based on number of populated cells

[spectraflame]

In column A there are up to 3 digit numbers with a decimal entered.
In column B there are up to 3 digits numbers with a decimal entered.

If the average of (A1-B1)/A1 is greater than or equal to 85% there is no need for additional numbers to be added. Meaning if I run my test on the 1st day of the month and the result is greater than 85%, there is no need for an additional test.

If the average of (A1-B1)/A1 is less than 85% there is a need to run a second test sometime within the same month. When those values are entered in the same columns (different cells), I will then need to take the average of both numbers in column A and the average of both numbers in column B and then average those sums to see if the result is greater than 85%. This process could technically be repeated each day of the month until the total average is greater than or equal to 85%.

Variables: The tests are not run on the same day of each month nor are the retests always done the following day.

I would like to see if the end result could be calculated automatically by just letting the operators manually key in the test results in the appropriate column each day.

I am not sure where to begin with this. My initial thought was to just have the operators key in the values in a small table that would calculate everything for them. My concern is mistyping the data. The results are submitted for State approval so accuracy is important.

Any suggestions would be appreciated.

 

[NiMip]

In column A there are up to 3 digit numbers with a decimal entered.
In column B there are up to 3 digits numbers with a decimal entered.

If the average of (A1-B1)/A1 is greater than or equal to 85% there is no need for additional numbers to be added. Meaning if I run my test on the 1st day of the month and the result is greater than 85%, there is no need for an additional test.

If the average of (A1-B1)/A1 is less than 85% there is a need to run a second test sometime within the same month. When those values are entered in the same columns (different cells), I will then need to take the average of both numbers in column A and the average of both numbers in column B and then average those sums to see if the result is greater than 85%. This process could technically be repeated each day of the month until the total average is greater than or equal to 85%.

Variables: The tests are not run on the same day of each month nor are the retests always done the following day.

I would like to see if the end result could be calculated automatically by just letting the operators manually key in the test results in the appropriate column each day.

I am not sure where to begin with this. My initial thought was to just have the operators key in the values in a small table that would calculate everything for them. My concern is mistyping the data. The results are submitted for State approval so accuracy is important.

Any suggestions would be appreciated.

I'm not 100% sure but to me it reads as if you're after =(AVERAGE(A:A)-AVERAGE(B:B))/(AVERAGE(A:A)
That way, whether there is one entry or thirty, it'll be averaging the values in each column before making the (A1-B1)/A1 calculation. You could then have something like =IF(result<0.85,"Another test required","Satisfactory")

If you want to incorporate dates, so the same sheet can be used month after month, it'll be a little more complicated. If you get the person to the date of their entry into column C, the formula is:
=(AVERAGE(IF(MONTH(C:C)=MONTH(TODAY()),A:A))-AVERAGE(IF(MONTH(C:C)=MONTH(TODAY()),B:B)))/AVERAGE(IF(MONTH(C:C)=MONTH(TODAY()),A:A))

Note that this required you to press CONTROL+SHIFT+ENTER when entering the formula, as it uses arrays. You also can't have column headings above the data, as it seems to mess with the formula.