Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to find additional ranges outside of min, max, and average
Excel 2003/2013
Win XP/7 (using 2 different computers - needs to work on both) I have a range of unsorted data consisting of a row. =MAX(E16:BB16) gives me the highest value =AVERAGE(E16:BB16) gives me the average value =MIN(E16:BB16) gives me the lowest value How do I get the value of the range between the maximum and the average? And the value between the average and the minimum? Can I do it with one formula - perhaps an array formula? - or do I need to use helper cells? This is for my personal use, so I don't mind using helper cells. Ultimately I want a range that looks like this: Highest value know how Middle-High value Middle value know how Middle-Low value Lowest value know how Any thoughts would be greatly appreciated. :) Susan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to find additional ranges outside of min, max, and average
Hi Susan,
Am Sat, 17 Aug 2013 14:36:00 -0700 (PDT) schrieb Susan: How do I get the value of the range between the maximum and the average? try: =SUMIF(E16:BB16,"="&AVERAGE(E16:BB16))/COUNTIF(E16:BB16,"="&AVERAGE(E16:BB16)) And the value between the average and the minimum? =SUMIF(E16:BB16,"<="&AVERAGE(E16:BB16))/COUNTIF(E16:BB16,"<="&AVERAGE(E16:BB16)) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to find additional ranges outside of min, max, and average
Thank you, as usual, Claus!
Works perfectly! Susan :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAX AVERAGE MIN with additional criteria, ignoring blank cells | Excel Discussion (Misc queries) | |||
AVERAGESIFS Function - average cells that fall within a date rangeand meet additional criteria | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Newbie: Find additional text strings | Excel Programming | |||
Column chart with additional "average" column | Charts and Charting in Excel |