Average Help

  • Thread starter Thread starter Rose Davis
  • Start date Start date
R

Rose Davis

Can anyone help with this. I have 1 column with 1000 rows and need to
determine the average of each row that is greater than 95 and less than 95,
but I also need to identify the row that is greater than 95 =R and less than
95 = NR I am not able to sort the row. The information needs to stay in the
current order.

88.1042 NR average amt
88.8367 NR average amt
92.1021
88.7146
94.1467
91.7664
88.8367
92.3157
96.8933 R average amt
95.7031
94.9707
96.5576
99.7925
99.8535
98.9075
102.1423
102.8137
101.8066
102.2644
102.478
102.7527
 
Here's one formula:

=AVERAGE(IF((A1:A1000>95)*(B1:B1000="R"),A1:A1000))

Array-entered, meaning press ctrl/shift/enter. You should
be able to figure out the other one.

HTH
Jason
Atlanta, GA
 
Can anyone help with this. I have 1 column with 1000 rows and need to
determine the average of each row that is greater than 95 and less than 95,
but I also need to identify the row that is greater than 95 =R and less than
95 = NR I am not able to sort the row. The information needs to stay in the
current order.

88.1042 NR average amt
88.8367 NR average amt
92.1021
88.7146
94.1467
91.7664
88.8367
92.3157
96.8933 R average amt
95.7031
94.9707
96.5576
99.7925
99.8535
98.9075
102.1423
102.8137
101.8066
102.2644
102.478
102.7527
Click to expand...

I'm not sure I'm understanding you entirely. Perhaps the following will help:

To get the average of all of the rows that contain a value greater than 95, use
this formula: =SUMIF(A1:A1000,">95")/COUNTIF(A1:A1000,">95")

To get the average of all of the rows that contain a value less than 95, use
this formula: =SUMIF(A1:A1000,"<95")/COUNTIF(A1:A1000,"<95")

Note that, per your request, cells with a value of exactly 95 will be excluded
from either calculation.

So far as identifying which cells are greater and which cells are less, a
simple IF function should be able to sort that out. You may want to combine
that with conditional formatting.

--ron
 
Back
Top