Cse ?

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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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

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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

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

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

You were close <g>!

Try, array-entered with CSE*: =MIN(IF(F39:M39>0,F39:M39))
*press CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> 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: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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
|
| 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 <at>yahoo<dot>com
----

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:M39>0,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 <at>yahoo<dot>com
----

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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

Glad to hear that, John !
Thanks for the closure !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"JohnHill" <[email protected]> wrote in
message news:[email protected]...
>
> 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