maximum repeated value
 

I'm trying to create an excel formula to give me the maximum repeaded
value in a range of numbers.
example:
5 I need to know the maximum value that occurs more
then once.
5 In this case the number would be 8.
8 Would I be able to write a formula that can do
this, it really
5 would help me out.
1
9
8

maximum repeated value
 

Here's one way using an ARRAY FORMULA*:
For a list of values in A1:A10

The max repeated value
B1: =MAX(IF(COUNTIF(A1:A10,A1:A10)1,A1:A10))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"markag" wrote:

I'm trying to create an excel formula to give me the maximum repeaded
value in a range of numbers.
example:
5 I need to know the maximum value that occurs more
then once.
5 In this case the number would be 8.
8 Would I be able to write a formula that can do
this, it really
5 would help me out.
1
9
8

maximum repeated value
 

This helped a lot. I do have a couple situations where I need to do the same
thing, but because of the way my data arranged I'm having trouble.

how woud I do it if my data is arranged like this?
------------------------------------------------------------------------
| 1 | 3 | 0 | 3 | 4 | 8 | 4 | 5 | 9 | 8 | 4 | 6 |
------------------------------------------------------------------------

2 4 5 6 3 5 6 3
3

------------------------------------------------------------------------
| 1 | 3 | 3 | 1 | 4 | 8 | 7 | 5 | 9 | 3 | 4 | 4 |
------------------------------------------------------------------------

The only information that I want is the information that is in the boxes. I
don't want the numbers that aren't in the boxes and I can't rearrange my data
at all. I have to leave it how it is. How can I select only the data that I
want and have this formula still apply. I've tried holding ctrl and choosing
the data but I keep getting errors telling me that I have made a mistake in
my formula


"Ron Coderre" wrote:

Here's one way using an ARRAY FORMULA*:
For a list of values in A1:A10

The max repeated value
B1: =MAX(IF(COUNTIF(A1:A10,A1:A10)1,A1:A10))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"markag" wrote:

I'm trying to create an excel formula to give me the maximum repeaded
value in a range of numbers.
example:
5 I need to know the maximum value that occurs more
then once.
5 In this case the number would be 8.
8 Would I be able to write a formula that can do
this, it really
5 would help me out.
1
9
8