Cse ?

[JohnHill]

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:M39>0) and the Control / Shift / Enter ??

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

Thanks

JH

 

[Max]

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER

 

[JohnHill]

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

 

[Max]

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

Paste into the formula bar for say, N39:
=MIN(IF(F39:M39>0,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:M39>0,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+0>0,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.

 

[ScottO]

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


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?action=getinfo&userid=25171
| View this thread: http://www.excelforum.com/showthread.php?threadid=386666
|

 

[Max]

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

 

[Harlan Grove]

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.

 

[Harlan Grove]

Max wrote...

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER

....

Possible to avoid array formulas by using

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

 

[Max]

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.

 

[JohnHill]

Max,

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

Thanks very much.

Joh

 

[Max]

Glad to hear that, John !
Thanks for the closure !