Average Based on number of populated cells

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
830
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top