Minimum value

[Svgmassive]

I am looking to get the minimum value excluding zeros in the range listed below thanks.
$H$5,$H$7:$H$9,$H$11:$H$14,$H$16:$H$18,$H$20

 

[pgc01]

Hi

Can you explain?

For ex.:

- the cells in the range have numerical values positive or zero

- the cells in the range have numerical values positive, negative or zero

- the cells in the range have text values or numerical values positive, negative or zero

?

 

[Aladin Akyurek]

I am looking to get the minimum value excluding zeros in the range listed below thanks.
$H$5,$H$7:$H$9,$H$11:$H$14,$H$16:$H$18,$H$20

Try the following...

=SMALL((H5,H7:H9,H11:H14,H16:H18,H20),INDEX(FREQUENCY((H5,H7:H9,H11:H14,H16:H18,H20),0),1)+1)

 

[Svgmassive]

thanks can this be applied to conditional formatting

 

[Aladin Akyurek]

thanks

You are welcome.

can this be applied to conditional formatting

Care to describe what you have in mind?

 

[Svgmassive]

when i tried to use conditional formatting to highlight the cells.
=SMALL((H5,H7:H9,H11:H14,H16:H18,H20),INDEX(FREQUENCY((H5,H7:H9,H11:H14,H16:H18,H20),0),1)+1)
i get an error message.you may not use union,intersections or array constants for conditional formatting.excel 2003.
Thanks

 

[Aladin Akyurek]

when i tried to use conditional formatting to highlight the cells.
=SMALL((H5,H7:H9,H11:H14,H16:H18,H20),INDEX(FREQUENCY((H5,H7:H9,H11:H14,H16:H18,H20),0),1)+1)
i get an error message.you may not use union,intersections or array constants for conditional formatting.excel 2003.
Thanks

Let L5 house the suggested formula.

Select L5, go to the Name Box on the Formula Bar, type MinValue, and hit enter.

Select H5, hold down the control key, and select H7:H19, etc. all of the relevant cells and ranges.
Activate Format | Conditional Formatting.
Choose Cell Value is and equal to.
Enter =MinValue in the white box.
Activate the Format button.
Choose the desired formatting.
Click OK twice successively.

 

[PCL]

A variation just for the fun.
Make a name (MyData) using range H5,H7:H9,H11:H14,H16:H18,H20
Minimum can be
=IF(SMALL(MyData,1)=0,SMALL(MyData,2),SMALL(MyData,1))
Make a name (MyMin) using previous formula
=IF(SMALL(MyData,1)=0,SMALL(MyData,2),SMALL(MyData,1))
Make a condition format for H5 using formula = myMin
Make a copy of H5 and a Special Paste with format

 

[Svgmassive]

that worked great thanks so much guys.I would like also to find the max value and the top ten values in the range.thanks

 

[Aladin Akyurek]

that worked great thanks so much guys.I would like also to find the max value and the top ten values in the range.thanks

It would be rather helpful if you provided more directed comments at the forwarded proposals. That way you can avoid requesting the same thing many times. That said...

Since MAX is not affected bythe presence of zero's (and negative numbers)...

=MAX(H5,H7:H9,H11:H14,H16:H18,H20)

J5: 10

This specifies that you want Top 10 larges scores from the set of the non-contiguous ranges.

J7: List

J8, just enter and copy down:

=IF(ROWS($J$8:J8)<=$J$5,
    LARGE(($H$5,$H$7:$H$9,$H$11:$H$14,$H$16:$H$18,$H$20),
      ROWS($J$8:J8)),"")

The foregoing set up was alsready suggested to you in an earlier thread of yours. See post #5 in:

http://www.mrexcel.com/forum/showthread.php?t=538989

This set up will list 0's (and neg numbers) if they fall within the Top 10 set.

If the Top 10 set must avoid 0's and negative numbers:

J5: 10

J6:

=MIN(J5,INDEX(FREQUENCY((H5,H7:H9,H11:H14,H16:H18,H20),0),2))

J7: List

J8, just enter and copy down:

=IF(ROWS($J$8:J8)<=$J$6,
    LARGE(($H$5,$H$7:$H$9,$H$11:$H$14,$H$16:$H$18,$H$20),
      ROWS($J$8:J8)),"")

Does these help?