Trying to find additional ranges outside of min, max, and average


S

Susan

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
 
Ad

Advertisements

C

Claus Busch

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top