Derivation of min and max values
 

Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

You can probably achieve this with conditional formatting.

If you select the range and then go to conditional formatting and select
Fomula is
=RANK(E3,$A$1:$BL$96,1)<=10
Select the required format.

Enter a second condition where formula is
=RANK(E3,$A$1:$BL$96,0)<=10
and select format for largest ten.

Rowan
"bandy2000" wrote:

Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

Thanks for the info

I looked it up and the "ref" in your Example "E3" is the problem. I would
have to do this rank function for every cell. I was looking for a faster way
to do it. Do you (or anybody) have other Ideas?

"Rowan" wrote:

You can probably achieve this with conditional formatting.

If you select the range and then go to conditional formatting and select
Fomula is
=RANK(E3,$A$1:$BL$96,1)<=10
Select the required format.

Enter a second condition where formula is
=RANK(E3,$A$1:$BL$96,0)<=10
and select format for largest ten.

Rowan
"bandy2000" wrote:

Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

Assuming my range starts at cell E3 I select the whole range so that E3 is
still the activecell. Then enter the conditional format as posted and Excel
will automatically change the E3 to reference each cell in the range.

"bandy2000" wrote:

Thanks for the info

I looked it up and the "ref" in your Example "E3" is the problem. I would
have to do this rank function for every cell. I was looking for a faster way
to do it. Do you (or anybody) have other Ideas?

"Rowan" wrote:

You can probably achieve this with conditional formatting.

If you select the range and then go to conditional formatting and select
Fomula is
=RANK(E3,$A$1:$BL$96,1)<=10
Select the required format.

Enter a second condition where formula is
=RANK(E3,$A$1:$BL$96,0)<=10
and select format for largest ten.

Rowan
"bandy2000" wrote:

Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

Your code produces in my worksheet a circular reference.

I just tried to get the rank function in VBA. That produced major problems.
In the moment that looks not promising. VBA was not able to use the rank
function.



"Rowan" wrote:

Assuming my range starts at cell E3 I select the whole range so that E3 is
still the activecell. Then enter the conditional format as posted and Excel
will automatically change the E3 to reference each cell in the range.

"bandy2000" wrote:

Thanks for the info

I looked it up and the "ref" in your Example "E3" is the problem. I would
have to do this rank function for every cell. I was looking for a faster way
to do it. Do you (or anybody) have other Ideas?

"Rowan" wrote:

You can probably achieve this with conditional formatting.

If you select the range and then go to conditional formatting and select
Fomula is
=RANK(E3,$A$1:$BL$96,1)<=10
Select the required format.

Enter a second condition where formula is
=RANK(E3,$A$1:$BL$96,0)<=10
and select format for largest ten.

Rowan
"bandy2000" wrote:

Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

Hi,
Conditional Format.....assuming range is A1:CR64 then in cell A1 put
the conditional format..

A1<=SMALL($A$1:$CR$64,10) for the smallest 10 and
A1=LARGE($A$1:$CR$64,10) for the largest 10...

and whatever format you want of course...this will colour the relevant
cells...

Hth,
Oli

Derivation of min and max values
 

I would suggest a small change to the first to not count blank cells

A1<=AND(LEN(A1)0,SMALL($A$1:$CR$64,10))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
Hi,
Conditional Format.....assuming range is A1:CR64 then in cell A1 put
the conditional format..

A1<=SMALL($A$1:$CR$64,10) for the smallest 10 and
A1=LARGE($A$1:$CR$64,10) for the largest 10...

and whatever format you want of course...this will colour the relevant
cells...

Hth,
Oli

Derivation of min and max values
 

Hi,
he did say that every cell had data in...

Every cell in this field contains data.

OJ

Derivation of min and max values
 

But I never believe them :-)

Been bitten too many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
Hi,
he did say that every cell had data in...

Every cell in this field contains data.

OJ

Derivation of min and max values
 

Since you don't seem enamored with conditional formatting and you posted in
programming, here is some code that might help.

Change Range("A1") to the upper left corner of your range.

Sub MarkTheCells()
Dim rng as Range, rng1 as Range
Dim l as Double, s as Double
Dim cell as Range
set rng = range("A1").Resize(96,64)
rng.interior.ColorIndex = xlNone
l = application.Large(rng,10)
s = application.Small(rng,10)
for each cell in rng
if isnumeric(cell) and _
not isempty(cell) and cell.Text < "" then
if cell.Value = l then
cell.interior.colorIndex = 5
elseif cell.Value<= s then
cell.Interior.colorIndex = 3
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"bandy2000" wrote in message
...
Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select
them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

Thanks Tom,
that was what I was looking for. I made the necessary changes for my case
and it worked instantly.
When this Sub found a Cell to be within the top 10. How can I dereive the
information in which Cell this happened?

"Bob Phillips" wrote:

But I never believe them :-)

Been bitten too many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
Hi,
he did say that every cell had data in...

Every cell in this field contains data.

OJ

Derivation of min and max values
 

The name is Bob :-)

I don't understand the question. If you use CF, the cell where it occurs
will be coloured.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bandy2000" wrote in message
...
Thanks Tom,
that was what I was looking for. I made the necessary changes for my case
and it worked instantly.
When this Sub found a Cell to be within the top 10. How can I dereive the
information in which Cell this happened?

"Bob Phillips" wrote:

But I never believe them :-)

Been bitten too many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
Hi,
he did say that every cell had data in...

Every cell in this field contains data.

OJ

Derivation of min and max values
 

Thanks Tom,
that was what I was looking for. I made the necessary changes for my case
and it worked instantly.
When this Sub found a Cell to be within the top 10. How can I dereive the
information in which Cell (Row / Col) this happened?

"Tom Ogilvy" wrote:

Since you don't seem enamored with conditional formatting and you posted in
programming, here is some code that might help.

Change Range("A1") to the upper left corner of your range.

Sub MarkTheCells()
Dim rng as Range, rng1 as Range
Dim l as Double, s as Double
Dim cell as Range
set rng = range("A1").Resize(96,64)
rng.interior.ColorIndex = xlNone
l = application.Large(rng,10)
s = application.Small(rng,10)
for each cell in rng
if isnumeric(cell) and _
not isempty(cell) and cell.Text < "" then
if cell.Value = l then
cell.interior.colorIndex = 5
elseif cell.Value<= s then
cell.Interior.colorIndex = 3
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"bandy2000" wrote in message
...
Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select
them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

This post was regarding the earlier post from Tom Ogilvy. Somehow it didn't
place my post on the right spot where it should be place. :-)

"Bob Phillips" wrote:

The name is Bob :-)

I don't understand the question. If you use CF, the cell where it occurs
will be coloured.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bandy2000" wrote in message
...
Thanks Tom,
that was what I was looking for. I made the necessary changes for my case
and it worked instantly.
When this Sub found a Cell to be within the top 10. How can I dereive the
information in which Cell this happened?

"Bob Phillips" wrote:

But I never believe them :-)

Been bitten too many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
Hi,
he did say that every cell had data in...

Every cell in this field contains data.

OJ

Derivation of min and max values
 

the object Cell holds the information at the time when it colors the cell.

Sub MarkTheCells()
Dim rng as Range, rng1 as Range
Dim l as Double, s as Double
Dim cell as Range
set rng = range("A1").Resize(96,64)
rng.interior.ColorIndex = xlNone
l = application.Large(rng,10)
s = application.Small(rng,10)
for each cell in rng
if isnumeric(cell) and _
not isempty(cell) and cell.Text < "" then
if cell.Value = l then
cell.interior.colorIndex = 5
cell.Select
msgbox "Large Cell found at " & cell.Address
elseif cell.Value<= s then
cell.Interior.colorIndex = 3
cell.Select
msgbox "Small Cell found at " & cell.Address
end if
end if
Next
End Sub

the select and msgbox are for example since I don't know what you want to do
with the information.

--
Regards,
Tom Ogilvy



"bandy2000" wrote in message
...
Thanks Tom,
that was what I was looking for. I made the necessary changes for my case
and it worked instantly.
When this Sub found a Cell to be within the top 10. How can I dereive the
information in which Cell this happened?

"Bob Phillips" wrote:

But I never believe them :-)

Been bitten too many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
Hi,
he did say that every cell had data in...

Every cell in this field contains data.

OJ

Derivation of min and max values
 

the object Cell holds the information at the time when it colors the cell.

Sub MarkTheCells()
Dim rng as Range, rng1 as Range
Dim l as Double, s as Double
Dim cell as Range
set rng = range("A1").Resize(96,64)
rng.interior.ColorIndex = xlNone
l = application.Large(rng,10)
s = application.Small(rng,10)
for each cell in rng
if isnumeric(cell) and _
not isempty(cell) and cell.Text < "" then
if cell.Value = l then
cell.interior.colorIndex = 5
cell.Select
msgbox "Large Cell found at " & cell.Address
elseif cell.Value<= s then
cell.Interior.colorIndex = 3
cell.Select
msgbox "Small Cell found at " & cell.Address
end if
end if
Next
End Sub

the select and msgbox are for example since I don't know what you want to do
with the information.

--
Regards,
Tom Ogilvy

"bandy2000" wrote in message
...
Thanks Tom,
that was what I was looking for. I made the necessary changes for my case
and it worked instantly.
When this Sub found a Cell to be within the top 10. How can I dereive the
information in which Cell (Row / Col) this happened?

"Tom Ogilvy" wrote:

Since you don't seem enamored with conditional formatting and you posted
in
programming, here is some code that might help.

Change Range("A1") to the upper left corner of your range.

Sub MarkTheCells()
Dim rng as Range, rng1 as Range
Dim l as Double, s as Double
Dim cell as Range
set rng = range("A1").Resize(96,64)
rng.interior.ColorIndex = xlNone
l = application.Large(rng,10)
s = application.Small(rng,10)
for each cell in rng
if isnumeric(cell) and _
not isempty(cell) and cell.Text < "" then
if cell.Value = l then
cell.interior.colorIndex = 5
elseif cell.Value<= s then
cell.Interior.colorIndex = 3
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"bandy2000" wrote in message
...
Hi

I have a data in a 96 by 64 field. Every cell in this field contains
data.
How can I find the 10 biggest and smalles Values and then maybe select
them
or change the
Background Colour or do any other change of the font?

Thanks

Derivation of min and max values
 

Hi Tom,

I tried to adopt your code in order to process several worksheets but I
encountered problems with setting the rangen and the application.large
function. I havn't found any information on this so I don't have a clue what
to change in order to process several worksheets.
I tried it like this but it's not working: (Do you know why?)

Sub MarkTheCells(SelRange As Range, worksheetname As String)
Dim l As Double, s As Double
Dim cell, SortRange As Range
Dim pos As Integer
Dim CellRow, CellCol As Long


pos = 0
SelRange.Interior.ColorIndex = xlNone
l = Application.Large(SelRange, 10)
s = Application.Small(SelRange, 10)
For Each cell In SelRange
If IsNumeric(cell) And _
Not IsEmpty(cell) And cell.Text < "" Then
If cell.Value = l Then
CellRow = cell.Row
CellCol = cell.Column
cell.Interior.ColorIndex = 3
Worksheets(worksheetname).Range("A1").Offset(20 + pos, 4).Value =
cell.Value
Worksheets(worksheetname).Range("A1").Offset(20 + pos, 3).Value =
Range("A1").Offset(CellRow - 1, 0).Value
Worksheets(worksheetname).Range("A1").Offset(20 + pos, 2).Value =
Range("A1").Offset(0, CellCol - 1).Value
pos = pos + 1
ElseIf cell.Value <= s Then
cell.Interior.ColorIndex = 5
End If
End If
Next



End Sub

"Tom Ogilvy" wrote:

Since you don't seem enamored with conditional formatting and you posted in
programming, here is some code that might help.

Change Range("A1") to the upper left corner of your range.

Sub MarkTheCells()
Dim rng as Range, rng1 as Range
Dim l as Double, s as Double
Dim cell as Range
set rng = range("A1").Resize(96,64)
rng.interior.ColorIndex = xlNone
l = application.Large(rng,10)
s = application.Small(rng,10)
for each cell in rng
if isnumeric(cell) and _
not isempty(cell) and cell.Text < "" then
if cell.Value = l then
cell.interior.colorIndex = 5
elseif cell.Value<= s then
cell.Interior.colorIndex = 3
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"bandy2000" wrote in message
...
Hi

I have a data in a 96 by 64 field. Every cell in this field contains data.
How can I find the 10 biggest and smalles Values and then maybe select
them
or change the
Background Colour or do any other change of the font?

Thanks