Formula to count cells ignoring highest and lowest values

[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

 

[Immanuel]

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.

 

[Alan]

Would'nt =COUNT(A1:A20)-2 do it?

 

[Ken Wright]

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

Would'nt =COUNT(A1:A20)-2 do it?

 

[Alan]

Right on both counts!

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