MAX() and MIN()

[Finance Guru]

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)

 

[JE McGimpsey]

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(C5>0,C5,"")
E5: =IF(C5<0,C5,"")

 

[Mike H]

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]

Thanks very much for the prompt response.

Finance Guru

 

[Finance Guru]

Mike H,

Many thanks for your answer. I will give a try
Finance Guru