Formula to count cells ignoring highest and lowest values

  • Thread starter Thread starter Baldy
  • Start date Start date
B

Baldy

Hi there

Can anyone advise whether it's possible to use VBA, or a formula, to
count a range of cells whilst ignoring the highest and lowest values in
that range?

Thanks
 
Assuming your range of cells is defined as "rng", use the following array
formula:

=SUM(IF((rng<MAX(rng))*(rng>MIN(rng)),1,0))

Remember to hit Ctrl-Shift Enter instead of just Enter after typing (or
pasting) in the formula.

/i.
 
All depends - OP really needs to clarify the question. Apply your logic to the
following:-

3
3
5
7
8
9
9
9

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Alan said:
Would'nt =COUNT(A1:A20)-2 do it?
Click to expand...
 
Right on both counts!
Ken Wright said:
All depends - OP really needs to clarify the question. Apply your logic to the
following:-

3
3
5
7
8
9
9
9

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --
Click to expand...
 
Back
Top