Re: Finding Min Cell values excluding zero in alternate columns

=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" <Elizabeth@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:907DF8C3-A1CA-432F-8850-CBB1765E8DAB@xxxxxxxxxxxxxxxx
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" <MichaelC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9BF784AA-C401-44AF-9F93-913171652DB2@xxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: Triangular Matrix
    ... in which zero "0" elements do not have representation. ... R odd and C> N-R: if either test fails, ... How many non-zeroes are in the rows before row R? ... How big must the linear array be? ...
    (comp.lang.c)
  • Re: Triangular Matrix
    ... in which zero "0" elements do not have representation. ... R odd and C> N-R: if either test fails, ... How many non-zeroes are in the rows before row R? ... How big must the linear array be? ...
    (comp.lang.c)
  • Re: Triangular Matrix
    ... in which zero "0" elements do not have representation. ... R odd and C> N-R: if either test fails, ... How many non-zeroes are in the rows before row R? ... How big must the linear array be? ...
    (comp.lang.c)
  • Re: Arrays of zero length
    ... The minor strangeness is that when you allocate an array to zero size, ... saying something like that allocating an array makes it undefined ... In f77, zero-size was disallowed, ...
    (comp.lang.fortran)
  • Re: should every thing be zero indexed?
    ... Other families of languages count from 1. ... its number is zero: I always start counting at zero". ... > as a fundamental concept in both the definition of array indices and ... > for I in foo'range loop ...
    (comp.programming)