Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ID Values
Hi wal,
do you have negative nymbers? happy holidays |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ID Values
no negatives
"driller" wrote: Hi wal, do you have negative nymbers? happy holidays |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using two values to report multiple values | Excel Discussion (Misc queries) | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
List of values | Excel Discussion (Misc queries) | |||
Referencing a specific number to more general values in a table. | Excel Worksheet Functions | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions |