Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Date Formula Problem - Leave date blank if Null

I have a multiconditional formula which finds the earliest date of all
entries where all these conditions are met. If an entry does not consist of
all these conditions, I just want the cell left blank. How do I do that? I
have tried putting ,"" at the end of the formula but it still doesnt work.
Any help will be greatly appreciated.
MIN(IF((report!$D$2:$D$3001=$A$3)*(report!$I$2:$I$ 3001=$A$11)*
(report!$J$2:$J$3001=$A14)*(report!$K$2:$K$3001=$A 15)*
(report!$B$2:$B$30010),report!$B$2:$B$3001))
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Date Formula Problem - Leave date blank if Null

You might be better off using conditional formatting to "hide" the zero. You
could write the blank into the formula but then it'll be twice as long.

=IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),MIN((c ond1)*(cond2)*(cond3)*(cond4)*(cond5)*rng),"")

Conditional Format:

Select the cell with the formula
Goto FormatConditional Formatting
Formula Is: =cell_ref=0
Set the Font color to be the same as the fill color
Ok out

Biff

"Gayla" wrote in message
...
I have a multiconditional formula which finds the earliest date of all
entries where all these conditions are met. If an entry does not consist
of
all these conditions, I just want the cell left blank. How do I do that?
I
have tried putting ,"" at the end of the formula but it still doesnt work.
Any help will be greatly appreciated.
MIN(IF((report!$D$2:$D$3001=$A$3)*(report!$I$2:$I$ 3001=$A$11)*
(report!$J$2:$J$3001=$A14)*(report!$K$2:$K$3001=$A 15)*
(report!$B$2:$B$30010),report!$B$2:$B$3001))



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Date Formula Problem - Leave date blank if Null

Eh!

Disregard that formula! See what happens when you're in a hurry to go to
dinner!

Biff

"T. Valko" wrote in message
...
You might be better off using conditional formatting to "hide" the zero.
You could write the blank into the formula but then it'll be twice as
long.

=IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),MIN((c ond1)*(cond2)*(cond3)*(cond4)*(cond5)*rng),"")

Conditional Format:

Select the cell with the formula
Goto FormatConditional Formatting
Formula Is: =cell_ref=0
Set the Font color to be the same as the fill color
Ok out

Biff

"Gayla" wrote in message
...
I have a multiconditional formula which finds the earliest date of all
entries where all these conditions are met. If an entry does not consist
of
all these conditions, I just want the cell left blank. How do I do that?
I
have tried putting ,"" at the end of the formula but it still doesnt
work.
Any help will be greatly appreciated.
MIN(IF((report!$D$2:$D$3001=$A$3)*(report!$I$2:$I$ 3001=$A$11)*
(report!$J$2:$J$3001=$A14)*(report!$K$2:$K$3001=$A 15)*
(report!$B$2:$B$30010),report!$B$2:$B$3001))





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Date Formula Problem - Leave date blank if Null

Try it like this (array entered):

=IF(SUM((cond1)*(cond2)*(cond3)*(cond4)*(cond5)),M IN(IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),rng) ),"")

Biff

"T. Valko" wrote in message
...
Eh!

Disregard that formula! See what happens when you're in a hurry to go to
dinner!

Biff

"T. Valko" wrote in message
...
You might be better off using conditional formatting to "hide" the zero.
You could write the blank into the formula but then it'll be twice as
long.

=IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),MIN((c ond1)*(cond2)*(cond3)*(cond4)*(cond5)*rng),"")

Conditional Format:

Select the cell with the formula
Goto FormatConditional Formatting
Formula Is: =cell_ref=0
Set the Font color to be the same as the fill color
Ok out

Biff

"Gayla" wrote in message
...
I have a multiconditional formula which finds the earliest date of all
entries where all these conditions are met. If an entry does not
consist of
all these conditions, I just want the cell left blank. How do I do
that? I
have tried putting ,"" at the end of the formula but it still doesnt
work.
Any help will be greatly appreciated.
MIN(IF((report!$D$2:$D$3001=$A$3)*(report!$I$2:$I$ 3001=$A$11)*
(report!$J$2:$J$3001=$A14)*(report!$K$2:$K$3001=$A 15)*
(report!$B$2:$B$30010),report!$B$2:$B$3001))







  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Date Formula Problem - Leave date blank if Null

Thanks for the info. I used your first suggestions about the conditional
formatting. I did not think of that. So I just used the option if cell
value =0 then I formatted the font color to match the cell background and it
works great!
Thanks again.

"T. Valko" wrote:

Try it like this (array entered):

=IF(SUM((cond1)*(cond2)*(cond3)*(cond4)*(cond5)),M IN(IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),rng) ),"")

Biff

"T. Valko" wrote in message
...
Eh!

Disregard that formula! See what happens when you're in a hurry to go to
dinner!

Biff

"T. Valko" wrote in message
...
You might be better off using conditional formatting to "hide" the zero.
You could write the blank into the formula but then it'll be twice as
long.

=IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),MIN((c ond1)*(cond2)*(cond3)*(cond4)*(cond5)*rng),"")

Conditional Format:

Select the cell with the formula
Goto FormatConditional Formatting
Formula Is: =cell_ref=0
Set the Font color to be the same as the fill color
Ok out

Biff

"Gayla" wrote in message
...
I have a multiconditional formula which finds the earliest date of all
entries where all these conditions are met. If an entry does not
consist of
all these conditions, I just want the cell left blank. How do I do
that? I
have tried putting ,"" at the end of the formula but it still doesnt
work.
Any help will be greatly appreciated.
MIN(IF((report!$D$2:$D$3001=$A$3)*(report!$I$2:$I$ 3001=$A$11)*
(report!$J$2:$J$3001=$A14)*(report!$K$2:$K$3001=$A 15)*
(report!$B$2:$B$30010),report!$B$2:$B$3001))









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Date Formula Problem - Leave date blank if Null

Yeah, I think that was your best option.

Thanks for the feedback!

Biff

"Gayla" wrote in message
...
Thanks for the info. I used your first suggestions about the conditional
formatting. I did not think of that. So I just used the option if cell
value =0 then I formatted the font color to match the cell background and
it
works great!
Thanks again.

"T. Valko" wrote:

Try it like this (array entered):

=IF(SUM((cond1)*(cond2)*(cond3)*(cond4)*(cond5)),M IN(IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),rng) ),"")

Biff

"T. Valko" wrote in message
...
Eh!

Disregard that formula! See what happens when you're in a hurry to go
to
dinner!

Biff

"T. Valko" wrote in message
...
You might be better off using conditional formatting to "hide" the
zero.
You could write the blank into the formula but then it'll be twice as
long.

=IF((cond1)*(cond2)*(cond3)*(cond4)*(cond5),MIN((c ond1)*(cond2)*(cond3)*(cond4)*(cond5)*rng),"")

Conditional Format:

Select the cell with the formula
Goto FormatConditional Formatting
Formula Is: =cell_ref=0
Set the Font color to be the same as the fill color
Ok out

Biff

"Gayla" wrote in message
...
I have a multiconditional formula which finds the earliest date of all
entries where all these conditions are met. If an entry does not
consist of
all these conditions, I just want the cell left blank. How do I do
that? I
have tried putting ,"" at the end of the formula but it still doesnt
work.
Any help will be greatly appreciated.
MIN(IF((report!$D$2:$D$3001=$A$3)*(report!$I$2:$I$ 3001=$A$11)*
(report!$J$2:$J$3001=$A14)*(report!$K$2:$K$3001=$A 15)*
(report!$B$2:$B$30010),report!$B$2:$B$3001))









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If Date Null Show Nothing Ardy Excel Worksheet Functions 7 January 25th 07 07:12 PM
COUNTIF says Null = Blank but Blank < Null Epinn Excel Worksheet Functions 4 October 25th 06 08:03 PM
Run formula only if values are enter, otherwise leave blank Karen Excel Worksheet Functions 2 August 26th 06 12:49 AM
Formula <0 leave cell blank Whit Excel Discussion (Misc queries) 3 August 13th 06 02:47 AM
Count number of days between dates BUT IF null to current date kathi Excel Worksheet Functions 1 February 14th 06 05:11 PM


All times are GMT +1. The time now is 12:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"