Averages

  • Thread starter Thread starter sailor
  • Start date Start date
S

sailor

I have a series of numbers in a row with text in some cells and blanks in
others. How can I throw out the largest one, two or three numbers and
average the remaining? The total number of cells to be thrown-out will be a
variable from row to row.
 
The below formula will return the average after ignoring the largest numbers
from row 1. The number of cells to be ignored is to be specified in cell B3.
Try and feedback...

=(SUM(1:1)-SUMPRODUCT(LARGE(1:1,ROW(INDIRECT("1:"&B3)))))/(COUNT(1:1)-B3)

If this post helps click Yes
 
sailor -

If you could rearrange your data to exclude the blanks and text, or if you
could create a new set of cells referring to only the numeric values, then
you could use the TRIMMEAN function.

- Mike
http://www.MikeMiddleton.com
 
It appears that this formula counts the blanks (zeros) and any cell that has
text in it. It did not give me the results I was looking for.
 
Back
Top