Hi ... first time here !!

Probably a simple problem. I've got a range of cells let's say F39:M39.
I want to select the lowest value 0. What I'd call a MINIF. No mention
in Excel. Some scouting around on the Web refers to CSE formulas. But I
can't seem to get it to work.

I'm trying .... =min(if(F39:M390) and the Control / Shift / Enter ??

Do I have to activate CSE in Excel first or am I just doing it wrong ?

Thanks

JH


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=386666

You were close <g!

Try, array-entered with CSE*: =MIN(IF(F39:M390,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JohnHill" wrote in
message ...

Hi ... first time here !!

Probably a simple problem. I've got a range of cells let's say F39:M39.
I want to select the lowest value 0. What I'd call a MINIF. No mention
in Excel. Some scouting around on the Web refers to CSE formulas. But I
can't seem to get it to work.

I'm trying .... =min(if(F39:M390) and the Control / Shift / Enter ??

Do I have to activate CSE in Excel first or am I just doing it wrong ?

Thanks

JH


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile:
http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=386666

Max,

Thanks, I tried that but got $0 as the answer. Two of the cells have $0
values. What and I doing wrong ? Do I have to activate something first
?

John


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=386666

Perhaps you could try implementing the formula in this manner ..

Paste into the formula bar for say, N39:
=MIN(IF(F39:M390,F39:M39))

Then with the cursor still inside the formula bar,
hold down CTRL+SHIFT keys, press ENTER

The formula should appear within curly braces { } inserted by Excel, viz.:
{=MIN(IF(F39:M390,F39:M39))}

Assuming the above's done ok but you still get "$0" displayed,
then try these diagnostics:

a. Check the formatting for the formula cell N39
Is it formatted to display as currency to zero d.p. ?
Try increasing the decimal places to display to more d.p.
It could be that the formula is already evaluating correctly
but the display hides the fine numbers <g

b. Any possibility of text numbers in F39:M39 ?
Maybe the whole lot are all text numbers?

Try instead in N39 (array-entered as before):
=MIN(IF(F39:M39+00,F39:M39+0))
Does it now compute properly?

You could also try converting the text numbers to real numbers by copying
any empty cell, then select / right-click on F39:M39 paste special check
"Add" OK.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JohnHill" wrote in
message ...

Max,

Thanks, I tried that but got $0 as the answer. Two of the cells have $0
values. What and I doing wrong ? Do I have to activate something first
?

John


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile:
http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=386666

Another (possibly less elegant) way ...
Assume the data is in a named range called "Rng", which INCLUDES at least
one zero. Question - Is it OK to include a row in the data range with a zero
value to ensure that there will always be one available?
If all assumptions are OK, then this formula should work for you ...
=SMALL(Rng,COUNT(Rng)-RANK(0,Rng)+2)

Rgds,
ScottO


"JohnHill" wrote in
message ...
|
| Max,
|
| Thanks, I tried that but got $0 as the answer. Two of the cells have $0
| values. What and I doing wrong ? Do I have to activate something first
| ?
|
| John
|
|
| --
| JohnHill
| ------------------------------------------------------------------------
| JohnHill's Profile:
http://www.excelforum.com/member.php...o&userid=25171
| View this thread: http://www.excelforum.com/showthread...hreadid=386666
|

... and guess I'm still not sure whether it finally worked for you?
John, are you still there? <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max wrote...
.. and guess I'm still not sure whether it finally worked for you?
John, are you still there? <g

Don't pester OPs. If you solution works, the absence of responses from
other regular respondents pointing out the flaws in your proposed
solution should serve as proof it worked.

Max wrote...
Try, array-entered with CSE*: =MIN(IF(F39:M390,F39:M39))
*press CTRL+SHIFT+ENTER
....

Possible to avoid array formulas by using

=SMALL(rng,COUNTIF(rng,"<=0")+1)

"Harlan Grove" wrote:
Don't pester OPs. ..

Hardly the case. It's been 3 long days since John called back saying he
still had some problems, Dad. And I was getting kind of anxious receiving
no closure to the exchanges we had had.

.. If you solution works, the absence of responses from
other regular respondents pointing out the flaws in your proposed
solution should serve as proof it worked.

Thanks. But I wouldn't want to misconstrue "silence" nor to implicitly
burden other regular, more experienced respondents with having to check
responses given and point out flaws. Just a dash of self-responsibility
there.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max,

Sorry for the delay in replying !! Yes it worked, that's very much. I'm
off to a meeting in Tasmania (I live in Brisbane Australia) on Thursday
this week. Needed this answer displayed as part of a presentation.

Thanks very much.

John


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=386666

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JohnHill" wrote in
message ...

Max,

Sorry for the delay in replying !! Yes it worked, that's very much. I'm
off to a meeting in Tasmania (I live in Brisbane Australia) on Thursday
this week. Needed this answer displayed as part of a presentation.

Thanks very much.

John