Finding Min Cell values excluding zero in alternate columns

[MichaelC]

I have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.

I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.

=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
I would greatly appreciate any help and thank you in advance for any offered.
MichaelC

[Biff]

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))

Biff

"MichaelC" <[email protected]> wrote in message
news:[email protected]...
>I have an array that is 1 row high by 16 columns wide.
> Each cell may contain a positive value, or a zero.
>
> I need a formula to find the "Minimum value that is greater than zero" in
> columns 1,3,5,7,9,11,13 and 15.
>
> =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
> need the minimum value that is greater than zero.
> If I use nested IF functions to exclude zeroes I run foul of the max of 7
> allowed.
> I would greatly appreciate any help and thank you in advance for any
> offered.
> MichaelC
>

[MichaelC]

Thank you so much - I just keep on learning every day thanks to people like
you.

"bpeltzer" wrote:

> If your input array is in A21:H21,
> =IF(MAX(A21:H21)<=0,"NA",LARGE(A21:H21,COUNTIF(A21:H21,">0")))
> should return the smallest positive value (or "NA") if there isn't one. The
> logic is to count the number of positive values, and use that as an argument
> to the LARGE function. --Bruce
>
> "MichaelC" wrote:
>
> > I have an array that is 1 row high by 16 columns wide.
> > Each cell may contain a positive value, or a zero.
> >
> > I need a formula to find the "Minimum value that is greater than zero" in
> > columns 1,3,5,7,9,11,13 and 15.
> >
> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
> > need the minimum value that is greater than zero.
> > If I use nested IF functions to exclude zeroes I run foul of the max of 7
> > allowed.
> > I would greatly appreciate any help and thank you in advance for any offered.
> > MichaelC
> >

[MichaelC]

Many thanks Biff - your solution also worked and helped me on another problem
that was stumping me.

"Biff" wrote:

> Hi!
>
> Try this:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>
> Biff
>
> "MichaelC" <[email protected]> wrote in message
> news:[email protected]...
> >I have an array that is 1 row high by 16 columns wide.
> > Each cell may contain a positive value, or a zero.
> >
> > I need a formula to find the "Minimum value that is greater than zero" in
> > columns 1,3,5,7,9,11,13 and 15.
> >
> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
> > need the minimum value that is greater than zero.
> > If I use nested IF functions to exclude zeroes I run foul of the max of 7
> > allowed.
> > I would greatly appreciate any help and thank you in advance for any
> > offered.
> > MichaelC
> >
>
>
>

[Elizabeth]

Biff:
I've been puzzling over your suggestion for days. It works, but I don't
understand it. Would you mind telling me what it is doing? I am fairly new
to arrays. I would appreciate your help. Thank you!
Elizabeth

"Biff" wrote:

> Hi!
>
> Try this:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>
> Biff
>
> "MichaelC" <[email protected]> wrote in message
> news:[email protected]...
> >I have an array that is 1 row high by 16 columns wide.
> > Each cell may contain a positive value, or a zero.
> >
> > I need a formula to find the "Minimum value that is greater than zero" in
> > columns 1,3,5,7,9,11,13 and 15.
> >
> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
> > need the minimum value that is greater than zero.
> > If I use nested IF functions to exclude zeroes I run foul of the max of 7
> > allowed.
> > I would greatly appreciate any help and thank you in advance for any
> > offered.
> > MichaelC
> >
>
>
>

[Biff]

>=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))

The columns are numbered from 1 to 256. Column A = 1, column B = 2, column C
= 3, etc.

Using the MOD function with a divisor of 2, all odd numbered columns will
return a mod of 1 and all the even numbered columns will return a mod of 0.
When these mod results are evaluated by the IF function the mods of 1 are
evaluated as TRUE and the mods of 0 are evaluated as FALSE.

So, this expression will return an array of 1's and 0's:

IF(MOD(COLUMN(A1:O1),2)

This expression which is testing the values in the range to be greater than
0 will return an array of TRUEs and FALSEs:

IF(A1:O1>0

Where both arrays evaluate to TRUE the corresponding value from the range
array, A1:O1, is passed to the MIN function.

Here's what it would look like using a smaller sample:

A1.....B1.....C1.....D1.....E1
65.....53.....-10.....55......4

IF(MOD(COLUMN(A1),2) = 1
IF(MOD(COLUMN(B1),2) = 0
IF(MOD(COLUMN(C1),2) = 1
IF(MOD(COLUMN(D1),2) = 0
IF(MOD(COLUMN(E1),2) = 1

IF(A1>0 = TRUE
IF(B1>0 = TRUE
IF(C1>0 = FALSE
IF(D1>0 = TRUE
IF(E1>0 = TRUE

IF(1,IF(TRUE = A1 = 65
IF(0,IF(TRUE = B1 = FALSE
IF(1,IF(FALSE = C1 = FALSE
IF(0,IF(TRUE = D1 = FALSE
IF(1,IF(TRUE = E1 = 4

=MIN({65,FALSE,FALSE,FALSE,4}) = 4

Biff

"Elizabeth" <[email protected]> wrote in message
news:[email protected]...
> Biff:
> I've been puzzling over your suggestion for days. It works, but I don't
> understand it. Would you mind telling me what it is doing? I am fairly
> new
> to arrays. I would appreciate your help. Thank you!
> Elizabeth
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>
>> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>>
>> Biff
>>
>> "MichaelC" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have an array that is 1 row high by 16 columns wide.
>> > Each cell may contain a positive value, or a zero.
>> >
>> > I need a formula to find the "Minimum value that is greater than zero"
>> > in
>> > columns 1,3,5,7,9,11,13 and 15.
>> >
>> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
>> > need the minimum value that is greater than zero.
>> > If I use nested IF functions to exclude zeroes I run foul of the max of
>> > 7
>> > allowed.
>> > I would greatly appreciate any help and thank you in advance for any
>> > offered.
>> > MichaelC
>> >
>>
>>
>>

[Elizabeth]

Biff:
Thank you so much for your thorough reply. Once again I'm humbled by the
knowledge within these Discussion Groups. May I confirm two things:

1) The non-array syntax of an IF statement is (per Microsoft Help):
IF(logical_test,value_if_true,value_if_false). Are you saying that the
syntax of an IF
statement within an array is: IF(logical_test,use_only_true_values)? In
other words, the IF statement within an array doesn't result in one value if
it is true and another value if it is false, but rather determines whether
each value within the function may continue to the next argument or not? If
that's the case, then I've had a huge breakthrough in understanding your
formula / arrays.

2) Are you saying True always = 1 and False always = 0? (If so, I probably
should have known that but didn't.)

Once again, THANK YOU SO MUCH.
Elizabeth

"Biff" wrote:
> >=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>
> The columns are numbered from 1 to 256. Column A = 1, column B = 2, column C
> = 3, etc.
>
> Using the MOD function with a divisor of 2, all odd numbered columns will
> return a mod of 1 and all the even numbered columns will return a mod of 0.
> When these mod results are evaluated by the IF function the mods of 1 are
> evaluated as TRUE and the mods of 0 are evaluated as FALSE.
>
> So, this expression will return an array of 1's and 0's:
>
> IF(MOD(COLUMN(A1:O1),2)
>
> This expression which is testing the values in the range to be greater than
> 0 will return an array of TRUEs and FALSEs:
>
> IF(A1:O1>0
>
> Where both arrays evaluate to TRUE the corresponding value from the range
> array, A1:O1, is passed to the MIN function.
>
> Here's what it would look like using a smaller sample:
>
> A1.....B1.....C1.....D1.....E1
> 65.....53.....-10.....55......4
>
> IF(MOD(COLUMN(A1),2) = 1
> IF(MOD(COLUMN(B1),2) = 0
> IF(MOD(COLUMN(C1),2) = 1
> IF(MOD(COLUMN(D1),2) = 0
> IF(MOD(COLUMN(E1),2) = 1
>
> IF(A1>0 = TRUE
> IF(B1>0 = TRUE
> IF(C1>0 = FALSE
> IF(D1>0 = TRUE
> IF(E1>0 = TRUE
>
> IF(1,IF(TRUE = A1 = 65
> IF(0,IF(TRUE = B1 = FALSE
> IF(1,IF(FALSE = C1 = FALSE
> IF(0,IF(TRUE = D1 = FALSE
> IF(1,IF(TRUE = E1 = 4
>
> =MIN({65,FALSE,FALSE,FALSE,4}) = 4
>
> Biff
>
> "Elizabeth" <[email protected]> wrote in message
> news:[email protected]...
> > Biff:
> > I've been puzzling over your suggestion for days. It works, but I don't
> > understand it. Would you mind telling me what it is doing? I am fairly
> > new
> > to arrays. I would appreciate your help. Thank you!
> > Elizabeth
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> Try this:
> >>
> >> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
> >>
> >> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
> >>
> >> Biff
> >>
> >> "MichaelC" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have an array that is 1 row high by 16 columns wide.
> >> > Each cell may contain a positive value, or a zero.
> >> >
> >> > I need a formula to find the "Minimum value that is greater than zero"
> >> > in
> >> > columns 1,3,5,7,9,11,13 and 15.
> >> >
> >> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
> >> > need the minimum value that is greater than zero.
> >> > If I use nested IF functions to exclude zeroes I run foul of the max of
> >> > 7
> >> > allowed.
> >> > I would greatly appreciate any help and thank you in advance for any
> >> > offered.
> >> > MichaelC
> >> >
> >>
> >>
> >>
>
>
>

[Biff]

Hi!

Question 1:

Basically, yes!

Using the formula as an example:

=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))

We're only interested in the elements that are TRUE so we can "ignore" those
elements that are FALSE. You'll notice that neither IF statement has a
value_if_false argument. If that argument is omitted the default return is
boolean FALSE.

You can include the value_if_false argument although it's superfluous and
could even cause problems if you don't use the correct type of value. For
instance, let's see what happens using these v_if_f arguments:

=MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,""),""))

=MIN({65,"","","",4}) = 4

=MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,"none"),"none"))

=MIN({65,"none","none","none",4}) = 4

=MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,0),0))

=MIN({65,0,0,0,4}) = 0

As you can see, using 0 as the v_if_f argument causes an incorrect result.

Question 2:

No, not ALWAYS. For example:

=MIN({65,FALSE,FALSE,FALSE,4}) = 4

Those FALSEs are not evaluated as 0 because the MIN function ignores logical
values (booleans).

When using a logical expression that returns a *numeric value*, ANY value
other than 0 evaluates to TRUE and 0 itself evaluates to FALSE. For example:

=IF(COUNT(A1:E1),"Yes","No") = Yes

In our example in this thread COUNT = 5, so COUNT(A1:E5) evaluates to TRUE

Put these values in some cells:

A1 = -1
A2 = -0.5
A3 = 0
A4 = 1
A5 = 10
A6 = xx

Then put this formula in B1 and copy down:

=IF(A1,TRUE,FALSE)

Biff

"Elizabeth" <[email protected]> wrote in message
news:[email protected]...
> Biff:
> Thank you so much for your thorough reply. Once again I'm humbled by the
> knowledge within these Discussion Groups. May I confirm two things:
>
> 1) The non-array syntax of an IF statement is (per Microsoft Help):
> IF(logical_test,value_if_true,value_if_false). Are you saying that the
> syntax of an IF
> statement within an array is: IF(logical_test,use_only_true_values)? In
> other words, the IF statement within an array doesn't result in one value
> if
> it is true and another value if it is false, but rather determines whether
> each value within the function may continue to the next argument or not?
> If
> that's the case, then I've had a huge breakthrough in understanding your
> formula / arrays.
>
> 2) Are you saying True always = 1 and False always = 0? (If so, I
> probably
> should have known that but didn't.)
>
> Once again, THANK YOU SO MUCH.
> Elizabeth
>
> "Biff" wrote:
>> >=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>>
>> The columns are numbered from 1 to 256. Column A = 1, column B = 2,
>> column C
>> = 3, etc.
>>
>> Using the MOD function with a divisor of 2, all odd numbered columns
>> will
>> return a mod of 1 and all the even numbered columns will return a mod of
>> 0.
>> When these mod results are evaluated by the IF function the mods of 1 are
>> evaluated as TRUE and the mods of 0 are evaluated as FALSE.
>>
>> So, this expression will return an array of 1's and 0's:
>>
>> IF(MOD(COLUMN(A1:O1),2)
>>
>> This expression which is testing the values in the range to be greater
>> than
>> 0 will return an array of TRUEs and FALSEs:
>>
>> IF(A1:O1>0
>>
>> Where both arrays evaluate to TRUE the corresponding value from the range
>> array, A1:O1, is passed to the MIN function.
>>
>> Here's what it would look like using a smaller sample:
>>
>> A1.....B1.....C1.....D1.....E1
>> 65.....53.....-10.....55......4
>>
>> IF(MOD(COLUMN(A1),2) = 1
>> IF(MOD(COLUMN(B1),2) = 0
>> IF(MOD(COLUMN(C1),2) = 1
>> IF(MOD(COLUMN(D1),2) = 0
>> IF(MOD(COLUMN(E1),2) = 1
>>
>> IF(A1>0 = TRUE
>> IF(B1>0 = TRUE
>> IF(C1>0 = FALSE
>> IF(D1>0 = TRUE
>> IF(E1>0 = TRUE
>>
>> IF(1,IF(TRUE = A1 = 65
>> IF(0,IF(TRUE = B1 = FALSE
>> IF(1,IF(FALSE = C1 = FALSE
>> IF(0,IF(TRUE = D1 = FALSE
>> IF(1,IF(TRUE = E1 = 4
>>
>> =MIN({65,FALSE,FALSE,FALSE,4}) = 4
>>
>> Biff
>>
>> "Elizabeth" <[email protected]> wrote in message
>> news:[email protected]...
>> > Biff:
>> > I've been puzzling over your suggestion for days. It works, but I
>> > don't
>> > understand it. Would you mind telling me what it is doing? I am
>> > fairly
>> > new
>> > to arrays. I would appreciate your help. Thank you!
>> > Elizabeth
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> Try this:
>> >>
>> >> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>> >>
>> >> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>> >>
>> >> Biff
>> >>
>> >> "MichaelC" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I have an array that is 1 row high by 16 columns wide.
>> >> > Each cell may contain a positive value, or a zero.
>> >> >
>> >> > I need a formula to find the "Minimum value that is greater than
>> >> > zero"
>> >> > in
>> >> > columns 1,3,5,7,9,11,13 and 15.
>> >> >
>> >> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value
>> >> > while I
>> >> > need the minimum value that is greater than zero.
>> >> > If I use nested IF functions to exclude zeroes I run foul of the max
>> >> > of
>> >> > 7
>> >> > allowed.
>> >> > I would greatly appreciate any help and thank you in advance for any
>> >> > offered.
>> >> > MichaelC
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[Elizabeth]

Biff:
Once again, thank you for your reply.
I had to read your reply many times to fully catch the multiple pieces of
information you were providing. I now understand, & have a copy of this
string in the front of my Excel reference notebook with these pieces of info
highlighted.
You were very kind to walk me through this - thank you!
Elizabeth


"Biff" wrote:

> Hi!
>
> Question 1:
>
> Basically, yes!
>
> Using the formula as an example:
>
> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>
> We're only interested in the elements that are TRUE so we can "ignore" those
> elements that are FALSE. You'll notice that neither IF statement has a
> value_if_false argument. If that argument is omitted the default return is
> boolean FALSE.
>
> You can include the value_if_false argument although it's superfluous and
> could even cause problems if you don't use the correct type of value. For
> instance, let's see what happens using these v_if_f arguments:
>
> =MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,""),""))
>
> =MIN({65,"","","",4}) = 4
>
> =MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,"none"),"none"))
>
> =MIN({65,"none","none","none",4}) = 4
>
> =MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,0),0))
>
> =MIN({65,0,0,0,4}) = 0
>
> As you can see, using 0 as the v_if_f argument causes an incorrect result.
>
> Question 2:
>
> No, not ALWAYS. For example:
>
> =MIN({65,FALSE,FALSE,FALSE,4}) = 4
>
> Those FALSEs are not evaluated as 0 because the MIN function ignores logical
> values (booleans).
>
> When using a logical expression that returns a *numeric value*, ANY value
> other than 0 evaluates to TRUE and 0 itself evaluates to FALSE. For example:
>
> =IF(COUNT(A1:E1),"Yes","No") = Yes
>
> In our example in this thread COUNT = 5, so COUNT(A1:E5) evaluates to TRUE
>
> Put these values in some cells:
>
> A1 = -1
> A2 = -0.5
> A3 = 0
> A4 = 1
> A5 = 10
> A6 = xx
>
> Then put this formula in B1 and copy down:
>
> =IF(A1,TRUE,FALSE)
>
> Biff
>
> "Elizabeth" <[email protected]> wrote in message
> news:[email protected]...
> > Biff:
> > Thank you so much for your thorough reply. Once again I'm humbled by the
> > knowledge within these Discussion Groups. May I confirm two things:
> >
> > 1) The non-array syntax of an IF statement is (per Microsoft Help):
> > IF(logical_test,value_if_true,value_if_false). Are you saying that the
> > syntax of an IF
> > statement within an array is: IF(logical_test,use_only_true_values)? In
> > other words, the IF statement within an array doesn't result in one value
> > if
> > it is true and another value if it is false, but rather determines whether
> > each value within the function may continue to the next argument or not?
> > If
> > that's the case, then I've had a huge breakthrough in understanding your
> > formula / arrays.
> >
> > 2) Are you saying True always = 1 and False always = 0? (If so, I
> > probably
> > should have known that but didn't.)
> >
> > Once again, THANK YOU SO MUCH.
> > Elizabeth
> >
> > "Biff" wrote:
> >> >=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
> >>
> >> The columns are numbered from 1 to 256. Column A = 1, column B = 2,
> >> column C
> >> = 3, etc.
> >>
> >> Using the MOD function with a divisor of 2, all odd numbered columns
> >> will
> >> return a mod of 1 and all the even numbered columns will return a mod of
> >> 0.
> >> When these mod results are evaluated by the IF function the mods of 1 are
> >> evaluated as TRUE and the mods of 0 are evaluated as FALSE.
> >>
> >> So, this expression will return an array of 1's and 0's:
> >>
> >> IF(MOD(COLUMN(A1:O1),2)
> >>
> >> This expression which is testing the values in the range to be greater
> >> than
> >> 0 will return an array of TRUEs and FALSEs:
> >>
> >> IF(A1:O1>0
> >>
> >> Where both arrays evaluate to TRUE the corresponding value from the range
> >> array, A1:O1, is passed to the MIN function.
> >>
> >> Here's what it would look like using a smaller sample:
> >>
> >> A1.....B1.....C1.....D1.....E1
> >> 65.....53.....-10.....55......4
> >>
> >> IF(MOD(COLUMN(A1),2) = 1
> >> IF(MOD(COLUMN(B1),2) = 0
> >> IF(MOD(COLUMN(C1),2) = 1
> >> IF(MOD(COLUMN(D1),2) = 0
> >> IF(MOD(COLUMN(E1),2) = 1
> >>
> >> IF(A1>0 = TRUE
> >> IF(B1>0 = TRUE
> >> IF(C1>0 = FALSE
> >> IF(D1>0 = TRUE
> >> IF(E1>0 = TRUE
> >>
> >> IF(1,IF(TRUE = A1 = 65
> >> IF(0,IF(TRUE = B1 = FALSE
> >> IF(1,IF(FALSE = C1 = FALSE
> >> IF(0,IF(TRUE = D1 = FALSE
> >> IF(1,IF(TRUE = E1 = 4
> >>
> >> =MIN({65,FALSE,FALSE,FALSE,4}) = 4
> >>
> >> Biff
> >>
> >> "Elizabeth" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Biff:
> >> > I've been puzzling over your suggestion for days. It works, but I
> >> > don't
> >> > understand it. Would you mind telling me what it is doing? I am
> >> > fairly
> >> > new
> >> > to arrays. I would appreciate your help. Thank you!
> >> > Elizabeth
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> Hi!
> >> >>
> >> >> Try this:
> >> >>
> >> >> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
> >> >>
> >> >> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
> >> >>
> >> >> Biff
> >> >>
> >> >> "MichaelC" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >I have an array that is 1 row high by 16 columns wide.
> >> >> > Each cell may contain a positive value, or a zero.
> >> >> >
> >> >> > I need a formula to find the "Minimum value that is greater than
> >> >> > zero"
> >> >> > in
> >> >> > columns 1,3,5,7,9,11,13 and 15.
> >> >> >
> >> >> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value
> >> >> > while I
> >> >> > need the minimum value that is greater than zero.
> >> >> > If I use nested IF functions to exclude zeroes I run foul of the max
> >> >> > of
> >> >> > 7
> >> >> > allowed.
> >> >> > I would greatly appreciate any help and thank you in advance for any
> >> >> > offered.
> >> >> > MichaelC
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Biff]

You're welcome. Thanks for the feedback!

Biff

"Elizabeth" <[email protected]> wrote in message
news:[email protected]...
> Biff:
> Once again, thank you for your reply.
> I had to read your reply many times to fully catch the multiple pieces of
> information you were providing. I now understand, & have a copy of this
> string in the front of my Excel reference notebook with these pieces of
> info
> highlighted.
> You were very kind to walk me through this - thank you!
> Elizabeth
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Question 1:
>>
>> Basically, yes!
>>
>> Using the formula as an example:
>>
>> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>>
>> We're only interested in the elements that are TRUE so we can "ignore"
>> those
>> elements that are FALSE. You'll notice that neither IF statement has a
>> value_if_false argument. If that argument is omitted the default return
>> is
>> boolean FALSE.
>>
>> You can include the value_if_false argument although it's superfluous and
>> could even cause problems if you don't use the correct type of value. For
>> instance, let's see what happens using these v_if_f arguments:
>>
>> =MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,""),""))
>>
>> =MIN({65,"","","",4}) = 4
>>
>> =MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,"none"),"none"))
>>
>> =MIN({65,"none","none","none",4}) = 4
>>
>> =MIN(IF(MOD(COLUMN(A1:E1),2),IF(A1:E1>0,A1:E1,0),0))
>>
>> =MIN({65,0,0,0,4}) = 0
>>
>> As you can see, using 0 as the v_if_f argument causes an incorrect
>> result.
>>
>> Question 2:
>>
>> No, not ALWAYS. For example:
>>
>> =MIN({65,FALSE,FALSE,FALSE,4}) = 4
>>
>> Those FALSEs are not evaluated as 0 because the MIN function ignores
>> logical
>> values (booleans).
>>
>> When using a logical expression that returns a *numeric value*, ANY value
>> other than 0 evaluates to TRUE and 0 itself evaluates to FALSE. For
>> example:
>>
>> =IF(COUNT(A1:E1),"Yes","No") = Yes
>>
>> In our example in this thread COUNT = 5, so COUNT(A1:E5) evaluates to
>> TRUE
>>
>> Put these values in some cells:
>>
>> A1 = -1
>> A2 = -0.5
>> A3 = 0
>> A4 = 1
>> A5 = 10
>> A6 = xx
>>
>> Then put this formula in B1 and copy down:
>>
>> =IF(A1,TRUE,FALSE)
>>
>> Biff
>>
>> "Elizabeth" <[email protected]> wrote in message
>> news:[email protected]...
>> > Biff:
>> > Thank you so much for your thorough reply. Once again I'm humbled by
>> > the
>> > knowledge within these Discussion Groups. May I confirm two things:
>> >
>> > 1) The non-array syntax of an IF statement is (per Microsoft Help):
>> > IF(logical_test,value_if_true,value_if_false). Are you saying that the
>> > syntax of an IF
>> > statement within an array is: IF(logical_test,use_only_true_values)?
>> > In
>> > other words, the IF statement within an array doesn't result in one
>> > value
>> > if
>> > it is true and another value if it is false, but rather determines
>> > whether
>> > each value within the function may continue to the next argument or
>> > not?
>> > If
>> > that's the case, then I've had a huge breakthrough in understanding
>> > your
>> > formula / arrays.
>> >
>> > 2) Are you saying True always = 1 and False always = 0? (If so, I
>> > probably
>> > should have known that but didn't.)
>> >
>> > Once again, THANK YOU SO MUCH.
>> > Elizabeth
>> >
>> > "Biff" wrote:
>> >> >=MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>> >>
>> >> The columns are numbered from 1 to 256. Column A = 1, column B = 2,
>> >> column C
>> >> = 3, etc.
>> >>
>> >> Using the MOD function with a divisor of 2, all odd numbered columns
>> >> will
>> >> return a mod of 1 and all the even numbered columns will return a mod
>> >> of
>> >> 0.
>> >> When these mod results are evaluated by the IF function the mods of 1
>> >> are
>> >> evaluated as TRUE and the mods of 0 are evaluated as FALSE.
>> >>
>> >> So, this expression will return an array of 1's and 0's:
>> >>
>> >> IF(MOD(COLUMN(A1:O1),2)
>> >>
>> >> This expression which is testing the values in the range to be greater
>> >> than
>> >> 0 will return an array of TRUEs and FALSEs:
>> >>
>> >> IF(A1:O1>0
>> >>
>> >> Where both arrays evaluate to TRUE the corresponding value from the
>> >> range
>> >> array, A1:O1, is passed to the MIN function.
>> >>
>> >> Here's what it would look like using a smaller sample:
>> >>
>> >> A1.....B1.....C1.....D1.....E1
>> >> 65.....53.....-10.....55......4
>> >>
>> >> IF(MOD(COLUMN(A1),2) = 1
>> >> IF(MOD(COLUMN(B1),2) = 0
>> >> IF(MOD(COLUMN(C1),2) = 1
>> >> IF(MOD(COLUMN(D1),2) = 0
>> >> IF(MOD(COLUMN(E1),2) = 1
>> >>
>> >> IF(A1>0 = TRUE
>> >> IF(B1>0 = TRUE
>> >> IF(C1>0 = FALSE
>> >> IF(D1>0 = TRUE
>> >> IF(E1>0 = TRUE
>> >>
>> >> IF(1,IF(TRUE = A1 = 65
>> >> IF(0,IF(TRUE = B1 = FALSE
>> >> IF(1,IF(FALSE = C1 = FALSE
>> >> IF(0,IF(TRUE = D1 = FALSE
>> >> IF(1,IF(TRUE = E1 = 4
>> >>
>> >> =MIN({65,FALSE,FALSE,FALSE,4}) = 4
>> >>
>> >> Biff
>> >>
>> >> "Elizabeth" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Biff:
>> >> > I've been puzzling over your suggestion for days. It works, but I
>> >> > don't
>> >> > understand it. Would you mind telling me what it is doing? I am
>> >> > fairly
>> >> > new
>> >> > to arrays. I would appreciate your help. Thank you!
>> >> > Elizabeth
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> Hi!
>> >> >>
>> >> >> Try this:
>> >> >>
>> >> >> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>> >> >>
>> >> >> =MIN(IF(MOD(COLUMN(A1:O1),2),IF(A1:O1>0,A1:O1)))
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "MichaelC" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >I have an array that is 1 row high by 16 columns wide.
>> >> >> > Each cell may contain a positive value, or a zero.
>> >> >> >
>> >> >> > I need a formula to find the "Minimum value that is greater than
>> >> >> > zero"
>> >> >> > in
>> >> >> > columns 1,3,5,7,9,11,13 and 15.
>> >> >> >
>> >> >> > =MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value
>> >> >> > while I
>> >> >> > need the minimum value that is greater than zero.
>> >> >> > If I use nested IF functions to exclude zeroes I run foul of the
>> >> >> > max
>> >> >> > of
>> >> >> > 7
>> >> >> > allowed.
>> >> >> > I would greatly appreciate any help and thank you in advance for
>> >> >> > any
>> >> >> > offered.
>> >> >> > MichaelC
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[mehulsagar]

Hi
I need to find the minimum value excluding zero but issue is my data is not in range like one value is in column Y then AK, AW, BI, BU, CG, CS, DE but row is same for all. How can i get the smallest value excluding zero.

Thanks & regards,
Mehul Sagar