| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Date Null Show Nothing
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
try
=if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
Is there any way we can have it not show any
dates if the range is null. maybe a slight if formula will NOT SHOW .. =IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5)) -- ***** birds of the same feather flock together.. "bj" wrote: try =if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
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 On Jan 25, 8:08 am, driller wrote: Is there any way we can have it not show any dates if the range is null.maybe a slight if formula will NOT SHOW .. =IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5)) -- ***** birds of the same feather flock together.. "bj" wrote: try =if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
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 On Jan 25, 5:17 pm, "Ardy" wrote: 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 On Jan 25, 8:08 am, driller wrote: Is there any way we can have it not show any dates if the range is null.maybe a slight if formula will NOT SHOW .. =IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5)) -- ***** birds of the same feather flock together.. "bj" wrote: try =if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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- Hide quoted text -- Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
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)) -- ***** birds of the same feather flock together.. "Pete_UK" wrote: 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 On Jan 25, 5:17 pm, "Ardy" wrote: 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 On Jan 25, 8:08 am, driller wrote: Is there any way we can have it not show any dates if the range is null.maybe a slight if formula will NOT SHOW .. =IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5)) -- ***** birds of the same feather flock together.. "bj" wrote: try =if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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- Hide quoted text -- Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
Thanks Guys Works Like A Charm
Ardy On Jan 25, 9:33 am, "Pete_UK" wrote: 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 On Jan 25, 5:17 pm, "Ardy" wrote: 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 On Jan 25, 8:08 am, driller wrote: Is there any way we can have it not show any dates if the range is null.maybe a slight if formula will NOT SHOW .. =IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5)) -- ***** birds of the same feather flock together.. "bj" wrote: try =if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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- Hide quoted text -- Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If Date Null Show Nothing
I changed it to drillers's version since it covers the OR option just
in case. Ardy On Jan 25, 10:01 am, driller wrote: 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)) -- ***** birds of the same feather flock together.. "Pete_UK" wrote: 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 On Jan 25, 5:17 pm, "Ardy" wrote: 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 On Jan 25, 8:08 am, driller wrote: Is there any way we can have it not show any dates if the range is null.maybe a slight if formula will NOT SHOW .. =IF(ISERROR(MAX(A1:A5)),"",MAX(A1:A5)) -- ***** birds of the same feather flock together.. "bj" wrote: try =if max(a1:a5)=0,"",Max(a1:a5)) "Ardy" wrote: 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- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Set a date as a constant to work back from to show only weekdays | Excel Discussion (Misc queries) | |||
| search for latest date | Excel Worksheet Functions | |||
| How do I show dates older than present date? | Excel Worksheet Functions | |||
| Is there a function to show future date | Excel Worksheet Functions | |||
| need to show date countdown | Excel Worksheet Functions | |||
Linear Mode
