Conditional Format - MIN, IF statements
 

Cells F19 through F21 have values of 4.09, 3.70, and 3.77, respectively. F27
has a value of zero. I want to set up a formula in F100 that identifies the
lowest value in these 4 cells excluding values that are zero. The answer in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

Array formula that must be entered using ctrl+shift+enter
=MIN(IF(G2:G220,G2:G22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77, respectively.
F27
has a value of zero. I want to set up a formula in F100 that identifies
the
lowest value in these 4 cells excluding values that are zero. The answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

=SMALL(F19:F27,COUNTIF(F19:F27,0)+1)


"bob" wrote:

Cells F19 through F21 have values of 4.09, 3.70, and 3.77, respectively. F27
has a value of zero. I want to set up a formula in F100 that identifies the
lowest value in these 4 cells excluding values that are zero. The answer in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

Either of the other two responses will work for you (change Don's ranges to
match Biff's) if there are no values in F22:F26; however, if there are
values there, then I'm not sure whether they can be modified, as posted, to
skip them. I'm sure there must be a better way but, off the top of my head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77, respectively.
F27
has a value of zero. I want to set up a formula in F100 that identifies
the
lowest value in these 4 cells excluding values that are zero. The answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

You don't need the 1000, Rick, you can just use

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27))

"Rick Rothstein (MVP - VB)" wrote:

Either of the other two responses will work for you (change Don's ranges to
match Biff's) if there are no values in F22:F26; however, if there are
values there, then I'm not sure whether they can be modified, as posted, to
skip them. I'm sure there must be a better way but, off the top of my head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77, respectively.
F27
has a value of zero. I want to set up a formula in F100 that identifies
the
lowest value in these 4 cells excluding values that are zero. The answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

Funny, I could swear I tried it without the 1000 and, when I did, the
formula returned 0; however, it is not doing that now. I must have screwed
up my original test somehow. Thanks for picking up on that.

Rick


"daddylonglegs" wrote in message
...
You don't need the 1000, Rick, you can just use

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27))

"Rick Rothstein (MVP - VB)" wrote:

Either of the other two responses will work for you (change Don's ranges
to
match Biff's) if there are no values in F22:F26; however, if there are
values there, then I'm not sure whether they can be modified, as posted,
to
skip them. I'm sure there must be a better way but, off the top of my
head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77,
respectively.
F27
has a value of zero. I want to set up a formula in F100 that identifies
the
lowest value in these 4 cells excluding values that are zero. The
answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

I tried your formula. If F27 = 0, 0 is the result. The forumla evaluates to:
=MIN(3.7, FALSE) and Excel returns 0. If it is =MIN({3.7, FALSE}), Excel
returns 3.7

"daddylonglegs" wrote in message
...
You don't need the 1000, Rick, you can just use

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27))

"Rick Rothstein (MVP - VB)" wrote:

Either of the other two responses will work for you (change Don's ranges
to
match Biff's) if there are no values in F22:F26; however, if there are
values there, then I'm not sure whether they can be modified, as posted,
to
skip them. I'm sure there must be a better way but, off the top of my
head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77,
respectively.
F27
has a value of zero. I want to set up a formula in F100 that identifies
the
lowest value in these 4 cells excluding values that are zero. The
answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

Excel help says that Excel ignores logical values if they are in array or
reference. If A1: =TRUE and A2: =FALSE, =MAX(A1:A2) = 0 and =MIN(A1:A2) = 0
whereas =MAX(TRUE, FALSE) = 1 and = MIN(TRUE, FALSE) = 0 So, TRUE amd FALSE
are not ignored when they are arguments to the function.

"Tyro" wrote in message
. ..
I tried your formula. If F27 = 0, 0 is the result. The forumla evaluates
to: =MIN(3.7, FALSE) and Excel returns 0. If it is =MIN({3.7, FALSE}),
Excel returns 3.7

"daddylonglegs" wrote in message
...
You don't need the 1000, Rick, you can just use

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27))

"Rick Rothstein (MVP - VB)" wrote:

Either of the other two responses will work for you (change Don's ranges
to
match Biff's) if there are no values in F22:F26; however, if there are
values there, then I'm not sure whether they can be modified, as posted,
to
skip them. I'm sure there must be a better way but, off the top of my
head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be
larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77,
respectively.
F27
has a value of zero. I want to set up a formula in F100 that
identifies
the
lowest value in these 4 cells excluding values that are zero. The
answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

Hello Tyro,

Yes, you're right, I realised that after I posted.

Sorry, Rick, it was me who needed more testing not you!

"Tyro" wrote:

Excel help says that Excel ignores logical values if they are in array or
reference. If A1: =TRUE and A2: =FALSE, =MAX(A1:A2) = 0 and =MIN(A1:A2) = 0
whereas =MAX(TRUE, FALSE) = 1 and = MIN(TRUE, FALSE) = 0 So, TRUE amd FALSE
are not ignored when they are arguments to the function.

"Tyro" wrote in message
. ..
I tried your formula. If F27 = 0, 0 is the result. The forumla evaluates
to: =MIN(3.7, FALSE) and Excel returns 0. If it is =MIN({3.7, FALSE}),
Excel returns 3.7

"daddylonglegs" wrote in message
...
You don't need the 1000, Rick, you can just use

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27))

"Rick Rothstein (MVP - VB)" wrote:

Either of the other two responses will work for you (change Don's ranges
to
match Biff's) if there are no values in F22:F26; however, if there are
values there, then I'm not sure whether they can be modified, as posted,
to
skip them. I'm sure there must be a better way but, off the top of my
head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be
larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77,
respectively.
F27
has a value of zero. I want to set up a formula in F100 that
identifies
the
lowest value in these 4 cells excluding values that are zero. The
answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob

Conditional Format - MIN, IF statements
 

I wonder why Excel is like that. It seems very inconsistent, and error prone
to me. Do all functions that ignore logical values in references or arrays,
take them into account if they are arguments in the same function?

Tyro

"daddylonglegs" wrote in message
...
Hello Tyro,

Yes, you're right, I realised that after I posted.

Sorry, Rick, it was me who needed more testing not you!

"Tyro" wrote:

Excel help says that Excel ignores logical values if they are in array or
reference. If A1: =TRUE and A2: =FALSE, =MAX(A1:A2) = 0 and =MIN(A1:A2) =
0
whereas =MAX(TRUE, FALSE) = 1 and = MIN(TRUE, FALSE) = 0 So, TRUE amd
FALSE
are not ignored when they are arguments to the function.

"Tyro" wrote in message
. ..
I tried your formula. If F27 = 0, 0 is the result. The forumla evaluates
to: =MIN(3.7, FALSE) and Excel returns 0. If it is =MIN({3.7, FALSE}),
Excel returns 3.7

"daddylonglegs" wrote in message
...
You don't need the 1000, Rick, you can just use

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27))

"Rick Rothstein (MVP - VB)" wrote:

Either of the other two responses will work for you (change Don's
ranges
to
match Biff's) if there are no values in F22:F26; however, if there
are
values there, then I'm not sure whether they can be modified, as
posted,
to
skip them. I'm sure there must be a better way but, off the top of my
head,
the following formula seems to work...

=MIN(MIN(IF(F19:F210,F19:F21)),IF(F270,F27,1000) )

Note this formula must be committed by pressing Ctrl+Shift+Enter.

The 1000 in the last number just needs to be a number that will be
larger
than any possible entry into the cells being tested.

Rick


"bob" wrote in message
...
Cells F19 through F21 have values of 4.09, 3.70, and 3.77,
respectively.
F27
has a value of zero. I want to set up a formula in F100 that
identifies
the
lowest value in these 4 cells excluding values that are zero. The
answer
in
this case should be 3.70.

Can anyone help?

Thanks,
Bob