If Date Null Show Nothing

A

Ardy

Hello All:
I am Using The =MAX(A1:A5) to pick the most recent date from the given
range. The Problem I am having is that if the Range is Null no date I
get the Date 1/0/1900, Is there any way we can have it not show any
dates if the range is null. I had the similar problem with calculating
percentiles and I tried to adopt variations of that to this but not
working

Ardy
 
D

driller

Is there any way we can have it not show any
maybe a slight if formula will NOT SHOW ..
=IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5))
 
A

Ardy

I Tried =if max(B7:B10)=0,"",Max(B7:B10)) it gives me an error
(B7:B10) is the actual Range,

I tried =IF(ISERROR(MAX(B7:B10)),"",MAX(B7:B10)) No errir but the date
still stays at 1/0/1900

Ardy
 
P

Pete_UK

BJ left a bracket out of his formula - try this:

=IF(MAX(B7:B10)=0,"",MAX(B7:B10))

The other formula (with ISERROR) will not work, as the MAX function
will not return an error in your situation - it will return zero, which
will get displayed as 1/0/1900.

Hope this helps.

Pete
 
D

driller

It is right to assume that B7:B10 will not return an error result ONLY if
such are made of hard data - not a result from a series of formulas,,,
to be rest assured in getting rid of any NULL and 1/0/1900

just adjust a little more with this formula

=IF(or(ISERROR(MAX(B7:B10)),MAX(B7:B10)=0),"",MAX(B7:B10))
 
A

Ardy

Thanks Guys Works Like A Charm
Ardy

BJ left a bracket out of his formula - try this:

=IF(MAX(B7:B10)=0,"",MAX(B7:B10))

The other formula (with ISERROR) will not work, as the MAX function
will not return an error in your situation - it will return zero, which
will get displayed as 1/0/1900.

Hope this helps.

Pete
Click to expand...
 
Top