Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averages | Excel Discussion (Misc queries) | |||
Help with Averages | Excel Worksheet Functions | |||
first ten and last ten averages | Excel Worksheet Functions | |||
30, 60 or 90 day averages | Excel Worksheet Functions | |||
averages | Excel Worksheet Functions |