ID Values
 

The Data
A B C
1 50 80 0
2 0 0 100
3 75 0 90
4 60 70 80
5 45 50 0

The three columns in each row can have 1, 2 or 3 non-zero numbers (but will
always have at least one). The numbers/zeros can be in any column. I need
column D to show the number if there is only one non-zero, the lowest if
there are two, and the middle number if there are three non zero. In the
example D1 and down would be 50, 100, 75, 70 and 45

Thanks
WAL50

ID Values
 

Try:
=IF(3-COUNTIF(A1:C1,0)=1,MAX(A1:C1),MEDIAN(A1:C1))

Scott

wal50 wrote:
The Data
A B C
1 50 80 0
2 0 0 100
3 75 0 90
4 60 70 80
5 45 50 0

The three columns in each row can have 1, 2 or 3 non-zero numbers (but will
always have at least one). The numbers/zeros can be in any column. I need
column D to show the number if there is only one non-zero, the lowest if
there are two, and the middle number if there are three non zero. In the
example D1 and down would be 50, 100, 75, 70 and 45

Thanks
WAL50

ID Values
 

=MIN(IF(A1:C10,A1:C1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"wal50" wrote in message
...
The Data
A B C
1 50 80 0
2 0 0 100
3 75 0 90
4 60 70 80
5 45 50 0

The three columns in each row can have 1, 2 or 3 non-zero numbers (but
will
always have at least one). The numbers/zeros can be in any column. I
need
column D to show the number if there is only one non-zero, the lowest if
there are two, and the middle number if there are three non zero. In the
example D1 and down would be 50, 100, 75, 70 and 45

Thanks
WAL50

ID Values
 

Forget that please, missed the middle bit.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
=MIN(IF(A1:C10,A1:C1))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"wal50" wrote in message
...
The Data
A B C
1 50 80 0
2 0 0 100
3 75 0 90
4 60 70 80
5 45 50 0

The three columns in each row can have 1, 2 or 3 non-zero numbers (but
will
always have at least one). The numbers/zeros can be in any column. I
need
column D to show the number if there is only one non-zero, the lowest if
there are two, and the middle number if there are three non zero. In
the
example D1 and down would be 50, 100, 75, 70 and 45

Thanks
WAL50

ID Values
 

Hi wal,
do you have negative nymbers?

happy holidays

ID Values
 

no negatives

"driller" wrote:

Hi wal,
do you have negative nymbers?

happy holidays

ID Values
 

Thanks Scott. Works great.

"Scott" wrote:

Try:
=IF(3-COUNTIF(A1:C1,0)=1,MAX(A1:C1),MEDIAN(A1:C1))

Scott

wal50 wrote:
The Data
A B C
1 50 80 0
2 0 0 100
3 75 0 90
4 60 70 80
5 45 50 0

The three columns in each row can have 1, 2 or 3 non-zero numbers (but will
always have at least one). The numbers/zeros can be in any column. I need
column D to show the number if there is only one non-zero, the lowest if
there are two, and the middle number if there are three non zero. In the
example D1 and down would be 50, 100, 75, 70 and 45

Thanks
WAL50