Averages
 

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.

Averages
 

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
---------------
Jacob Skaria


"sailor" wrote:

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.

Averages
 

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



"sailor" wrote in message
...
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.

Averages
 

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.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"sailor" wrote:

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.