I have a table populated with equations. I need to write a function to find
the smallest value in that table. However, I want to ignore the zero values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


--
Don Guillett
SalesAid Software

"OkieViking" wrote in message
...
I have a table populated with equations. I need to write a function to
find
the smallest value in that table. However, I want to ignore the zero
values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

=min(if(a1:a10<0,a1:a10))
array entered (control+shift+enter)

will find the smallest of all non-zero values

"OkieViking" wrote in message
...
I have a table populated with equations. I need to write a function to
find
the smallest value in that table. However, I want to ignore the zero
values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

One way

=LARGE(A1:A5,COUNTIF(A1:A5,"0"))


Regards,

Peo Sjoblom

"OkieViking" wrote:

I have a table populated with equations. I need to write a function to find
the smallest value in that table. However, I want to ignore the zero values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

No, not for me anyway. This is for work.

Thanks

"Don Guillett" wrote:

This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


--
Don Guillett
SalesAid Software

"OkieViking" wrote in message
...
I have a table populated with equations. I need to write a function to
find
the smallest value in that table. However, I want to ignore the zero
values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

The cells I am looking at are all on one row, but every other column. How do
I make the below work?

"Don Guillett" wrote:

This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


--
Don Guillett
SalesAid Software

"OkieViking" wrote in message
...
I have a table populated with equations. I need to write a function to
find
the smallest value in that table. However, I want to ignore the zero
values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

OkieViking wrote:
The cells I am looking at are all on one row, but every other column. How do
I make the below work?

[...]

=MIN(IF((MOD(ROW(A3:A8)-CELL("Row",A3:A8)+0,2)=0)*A3:A8,A3:A8))

which you need to confirm with control+shift+enter instead of the usual
enter.

Adjust the range to suit.

Make that...

=MIN(IF((MOD(ROW(A3:A8)-CELL("Row",A3:A8)+0,2)=0)*(A3:A8)0,A3:A8))

OkieViking wrote:
The cells I am looking at are all on one row, but every other column. How do
I make the below work?

"Don Guillett" wrote:


This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


--
Don Guillett
SalesAid Software

"OkieViking" wrote in message
...

I have a table populated with equations. I need to write a function to

find

the smallest value in that table. However, I want to ignore the zero

values.

From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

Hi, I found this formula very helpful, but how can I make it to work in a
conditional formating ?Gives error.

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


"OkieViking" wrote:

No, not for me anyway. This is for work.

Thanks

"Don Guillett" wrote:

This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


--
Don Guillett
SalesAid Software

"OkieViking" wrote in message
...
I have a table populated with equations. I need to write a function to
find
the smallest value in that table. However, I want to ignore the zero
values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?

Hi!

Assume you want to conditionally format the range C1:C10.

Select the range C1:C10
Goto FormatConditional Formatting
Formula is: =C1=MIN(IF(C$1:C$100,C$1:C$10))
Click the Format button
Select the desired style(s)
OK out

Biff

"Hecwill" wrote in message
...
Hi, I found this formula very helpful, but how can I make it to work in a
conditional formating ?Gives error.

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


"OkieViking" wrote:

No, not for me anyway. This is for work.

Thanks

"Don Guillett" wrote:

This question seems to be going around today. Homework?

non array enter with enter only
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

array - enter with control+shift+enter
=MIN(IF(C1:C100,C1:C10))


--
Don Guillett
SalesAid Software

"OkieViking" wrote in message
...
I have a table populated with equations. I need to write a function
to
find
the smallest value in that table. However, I want to ignore the zero
values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.

How do you do that?