Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX() and MIN()
Hi Excellers,
Can anyone suggest a solution for the following. I want to test C5 in D5 and E5 using the MAX() and MIN() functions. Dependent on the result Excel puts a Zere in the opposite Cell. eg. in E5 will be a Zero because C5 is positive. How can I get Excel to put a blank in this cell ( or any cell ) that does not satisy the MAX() or MIN() argument. I know how to do it using the IF( ) statement,but the MAX & MIN are new to me..... All help gratefully accepted ROW C D E 5 99 =MAX(0,C5) =MIN(0,C5) 6 -50 =MAX(0,C6) =MIN(0,C6) 7 45 =MAX(0,C7) =MIN(0,C7) 8 -54 =MAX(0,C8) =MIN(0,C8) 9 -54 =MAX(0,C9) =MIN(0,C9) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX() and MIN()
One way:
Max and Min will always return a numeric value, so you could just format the cells to display a blank if zero: Format/Cells/Number/Custom General;General;;@ OTOH, there's no reason not to use IF: D5: =IF(C50,C5,"") E5: =IF(C5<0,C5,"") In article , Finance Guru wrote: Hi Excellers, Can anyone suggest a solution for the following. I want to test C5 in D5 and E5 using the MAX() and MIN() functions. Dependent on the result Excel puts a Zere in the opposite Cell. eg. in E5 will be a Zero because C5 is positive. How can I get Excel to put a blank in this cell ( or any cell ) that does not satisy the MAX() or MIN() argument. I know how to do it using the IF( ) statement,but the MAX & MIN are new to me..... All help gratefully accepted ROW C D E 5 99 =MAX(0,C5) =MIN(0,C5) 6 -50 =MAX(0,C6) =MIN(0,C6) 7 45 =MAX(0,C7) =MIN(0,C7) 8 -54 =MAX(0,C8) =MIN(0,C8) 9 -54 =MAX(0,C9) =MIN(0,C9) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX() and MIN()
It depends what your definition of failing to satisfy the Max/Min argument is
but perhaps:- =IF(ISNUMBER(C5),MAX(0,C5),"") =IF(ISNUMBER(C5),MIN(0,C5),"") Mike "Finance Guru" wrote: Hi Excellers, Can anyone suggest a solution for the following. I want to test C5 in D5 and E5 using the MAX() and MIN() functions. Dependent on the result Excel puts a Zere in the opposite Cell. eg. in E5 will be a Zero because C5 is positive. How can I get Excel to put a blank in this cell ( or any cell ) that does not satisy the MAX() or MIN() argument. I know how to do it using the IF( ) statement,but the MAX & MIN are new to me..... All help gratefully accepted ROW C D E 5 99 =MAX(0,C5) =MIN(0,C5) 6 -50 =MAX(0,C6) =MIN(0,C6) 7 45 =MAX(0,C7) =MIN(0,C7) 8 -54 =MAX(0,C8) =MIN(0,C8) 9 -54 =MAX(0,C9) =MIN(0,C9) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX() and MIN()
Thanks very much for the prompt response.
Finance Guru "JE McGimpsey" wrote: One way: Max and Min will always return a numeric value, so you could just format the cells to display a blank if zero: Format/Cells/Number/Custom General;General;;@ OTOH, there's no reason not to use IF: D5: =IF(C50,C5,"") E5: =IF(C5<0,C5,"") In article , Finance Guru wrote: Hi Excellers, Can anyone suggest a solution for the following. I want to test C5 in D5 and E5 using the MAX() and MIN() functions. Dependent on the result Excel puts a Zere in the opposite Cell. eg. in E5 will be a Zero because C5 is positive. How can I get Excel to put a blank in this cell ( or any cell ) that does not satisy the MAX() or MIN() argument. I know how to do it using the IF( ) statement,but the MAX & MIN are new to me..... All help gratefully accepted ROW C D E 5 99 =MAX(0,C5) =MIN(0,C5) 6 -50 =MAX(0,C6) =MIN(0,C6) 7 45 =MAX(0,C7) =MIN(0,C7) 8 -54 =MAX(0,C8) =MIN(0,C8) 9 -54 =MAX(0,C9) =MIN(0,C9) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX() and MIN()
Mike H,
Many thanks for your answer. I will give a try Finance Guru "Mike H" wrote: It depends what your definition of failing to satisfy the Max/Min argument is but perhaps:- =IF(ISNUMBER(C5),MAX(0,C5),"") =IF(ISNUMBER(C5),MIN(0,C5),"") Mike "Finance Guru" wrote: Hi Excellers, Can anyone suggest a solution for the following. I want to test C5 in D5 and E5 using the MAX() and MIN() functions. Dependent on the result Excel puts a Zere in the opposite Cell. eg. in E5 will be a Zero because C5 is positive. How can I get Excel to put a blank in this cell ( or any cell ) that does not satisy the MAX() or MIN() argument. I know how to do it using the IF( ) statement,but the MAX & MIN are new to me..... All help gratefully accepted ROW C D E 5 99 =MAX(0,C5) =MIN(0,C5) 6 -50 =MAX(0,C6) =MIN(0,C6) 7 45 =MAX(0,C7) =MIN(0,C7) 8 -54 =MAX(0,C8) =MIN(0,C8) 9 -54 =MAX(0,C9) =MIN(0,C9) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|