Multiple IF Condition

[prkhan56]

I posted the following in worksheetfunctions.. but I understand this NG
is not active so posting it here again.

Hello All,

I am using Windows XP/Office 2003 and have the following problem:


I have a worksheet with 8 Columns as follows:
A B C D E
F G H
S# Ref. No Date CODE Company Problem Status ClosedDate


I wish to test two Cells viz. Column C (Date) and Column H
(ClosedDate).

If the date entered in Date is greater than or equal to Today(), the
value displayed in Column G will be "Active"

I have entered the following formula in G column which works ok except
it shows VALUE when copied down the column.. how can I modify the
following formula to display a " " (blank) if there is no value in
Column C (Date)

=IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",IF(SEARCH("#",$H2),"Closed"))

Also I wish to add another IF to the formula which should test the Date
in Column H and if the date entered is preceded by another symbol ~ for
eg ~20-03-05 then Column G should display "Cancelled"

My intention is to have "Active in Column G when the first entry is
made in Column C (Date). Later on Column G (Status) should show
Cancelled (if the date is preceded by a #) or Cancelled (if the date is
preceded by a ~)


Can anybody give me a clue or better suggestions please

TIA

Rashid

[Ron Rosenfeld]

On 26 Mar 2005 01:08:39 -0800, "prkhan56" <[email protected]> wrote:

>I have entered the following formula in G column which works ok except
>it shows VALUE when copied down the column.. how can I modify the
>following formula to display a " " (blank) if there is no value in
>Column C (Date)
>
>=IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",IF(SEARCH("#",$H2),"Closed"))

The VALUE error is coming from the SEARCH function.

Assuming that C2 can only be blank or a date (and you could use data validation
to ensure that), the following might be simpler:

=IF($C2>=TODAY(),"Active",IF(COUNTIF(H2,"*#*")>0,"Closed",""))

>My intention is to have "Active in Column G when the first entry is
>made in Column C (Date). Later on Column G (Status) should show
>Cancelled (if the date is preceded by a #) or Cancelled (if the date is
>preceded by a ~)

Is this a typo? Do you mean "Closed" if there's a "#" and Canceled if there is
a "~"?

If the latter, then:

=IF($C2>=TODAY(),"Active",IF(COUNTIF(H2,"*#*")>0,
"Closed",IF(COUNTIF(H2,"~~*")>0,"Cancelled","")))

Now then, the way you have posted this request, it seems that the contents of
C2 will take precedence. So if C2>=TODAY(), the formula will return Active no
matter what is in H2. The only way you can have a canceled or closed is if C2
is prior to TODAY().

Is that what you want?

If not, you may need to change the formula around a bit, so as to give the
contents of H2 precedence.






--ron

[Dave Peterson]

I think you want your original formula to be more like:

=IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",
IF(isnumber(SEARCH("#",$H2)),"Closed"),"Whatgoeshere")

the #value! error is coming from the search not finding the "#" in H2. But I
think you want another value "whatgoeshere" added to your formula.

I'd just make that check first for an empty C2.

=IF(C2="","",IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",
IF(ISNUMBER(SEARCH("#",$H2)),"Closed","whatgoeshere")))

And maybe...

=IF(LEFT(C2,1)="~","Cancelled",IF(C2="","",
IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",
IF(ISNUMBER(SEARCH("#",$H2)),"Closed","whatgoeshere"))))

But I do have a suggestion. Don't use " " in your formula. Use "". Then later
if you're testing, you can test for =if(a1="","onething","somethingelse")

If you use " " (or any multiple spaces), you'll have to do something like:
=if(trim(a1)="onething","somethingelse")

And I only checked for a leading tilde (~) in that date cell. If I were you,
I'd add another column to use for a cancel indicator.

By using another column, it'll make sorting by dates much easier, you can filter
by it and lots of other stuff.

(Try to keep each field in a separate column. It's lots easier combining fields
(when/if you need to) than to separate them.)


prkhan56 wrote:
>
> I posted the following in worksheetfunctions.. but I understand this NG
> is not active so posting it here again.
>
> Hello All,
>
> I am using Windows XP/Office 2003 and have the following problem:
>
> I have a worksheet with 8 Columns as follows:
> A B C D E
> F G H
> S# Ref. No Date CODE Company Problem Status ClosedDate
>
> I wish to test two Cells viz. Column C (Date) and Column H
> (ClosedDate).
>
> If the date entered in Date is greater than or equal to Today(), the
> value displayed in Column G will be "Active"
>
> I have entered the following formula in G column which works ok except
> it shows VALUE when copied down the column.. how can I modify the
> following formula to display a " " (blank) if there is no value in
> Column C (Date)
>
> =IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",IF(SEARCH("#",$H2),"Closed"))
>
> Also I wish to add another IF to the formula which should test the Date
> in Column H and if the date entered is preceded by another symbol ~ for
> eg ~20-03-05 then Column G should display "Cancelled"
>
> My intention is to have "Active in Column G when the first entry is
> made in Column C (Date). Later on Column G (Status) should show
> Cancelled (if the date is preceded by a #) or Cancelled (if the date is
> preceded by a ~)
>
> Can anybody give me a clue or better suggestions please
>
> TIA
>
> Rashid

--

Dave Peterson

[RagDyeR]

Do you realize you're saying that if you enter today's date in C2, tomorrow
it is *no longer* active (" >=Today() ")?

Do you really want that?

Also, what is this supposed to do?

IF(SEARCH("#",$H2),"Closed")

Shouldn't simply checking for a date in the "Closed" column trigger
"CLOSED"?

In reading between the lines, might this work for you?

=IF(AND(ISNUMBER(C2),H2=""),"Active",IF(OR(LEFT(C2)={"~","#"}),"Cancelled",I
F(ISNUMBER(H2),"Closed","")))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"prkhan56" <[email protected]> wrote in message
news:[email protected]...
I posted the following in worksheetfunctions.. but I understand this NG
is not active so posting it here again.

Hello All,

I am using Windows XP/Office 2003 and have the following problem:


I have a worksheet with 8 Columns as follows:
A B C D E
F G H
S# Ref. No Date CODE Company Problem Status ClosedDate


I wish to test two Cells viz. Column C (Date) and Column H
(ClosedDate).

If the date entered in Date is greater than or equal to Today(), the
value displayed in Column G will be "Active"

I have entered the following formula in G column which works ok except
it shows VALUE when copied down the column.. how can I modify the
following formula to display a " " (blank) if there is no value in
Column C (Date)

=IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",IF(SEARCH("#",$H2),"Closed"))

Also I wish to add another IF to the formula which should test the Date
in Column H and if the date entered is preceded by another symbol ~ for
eg ~20-03-05 then Column G should display "Cancelled"

My intention is to have "Active in Column G when the first entry is
made in Column C (Date). Later on Column G (Status) should show
Cancelled (if the date is preceded by a #) or Cancelled (if the date is
preceded by a ~)


Can anybody give me a clue or better suggestions please

TIA

Rashid

[prkhan56]

Sorry folks for the confusion. I will try to make it more clear

A B C D E F G H
S# Ref. No CallDate CODE Company Problem Status Date

I am keeping track of Company calls as they come in .. so normally the
day I enter in column C is Today (current date). So Column G (Status)
will be Active always.

The problem is then referred to the concerned person. When the Problem
is solved (Closed) or Cancelled (in rare cases) then the Date is
entered in Column H as on which date the problem was solved or
cancelled as the case may be.

I need to have the word 'Closed' in the Status (Column G) as soon
as a date is entered in Column H...but if it is cancelled for some
reason.. I will put the date in Column H ..
The following works but I loose the date format then..

=IF($C174>=TODAY(),"Active",IF(COUNTIF(H174,"*#*")>0,
"Closed",IF(COUNTIF(H174,"~~*")>0,"Cancelled","")))

I need to differentiate between the two Closed and Cancelled
Date..thats why I thought of using # (for Closed) and ~ (Cancelled).
Any better suggestion would be helpful.

Thanks to all who have helped once again
Rashid

[Nick Hodge]

Rashid

I think this does what you are looking for...

It leaves column G blank if no entry is in Call Date. It puts 'Active' if
there is a current or past date in C (Note:past date, the old formula was
current or future date >=TODAY()). It puts 'Closed' if a date is in H or
'Cancelled' if any non-date is in H, so ~01/01/2005 will suffice.

=IF(AND(H3="",C3=""),"",IF(AND(H3="",C3<=TODAY()),"Active",IF(ISERROR(DATE(YEAR(H3),MONTH(H3),DAY(H3))),"Cancelled","Closed")))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> Sorry folks for the confusion. I will try to make it more clear
>
> A B C D E F G H
> S# Ref. No CallDate CODE Company Problem Status Date
>
> I am keeping track of Company calls as they come in .. so normally the
> day I enter in column C is Today (current date). So Column G (Status)
> will be Active always.
>
> The problem is then referred to the concerned person. When the Problem
> is solved (Closed) or Cancelled (in rare cases) then the Date is
> entered in Column H as on which date the problem was solved or
> cancelled as the case may be.
>
> I need to have the word 'Closed' in the Status (Column G) as soon
> as a date is entered in Column H...but if it is cancelled for some
> reason.. I will put the date in Column H ..
> The following works but I loose the date format then..
>
> =IF($C174>=TODAY(),"Active",IF(COUNTIF(H174,"*#*")>0,
> "Closed",IF(COUNTIF(H174,"~~*")>0,"Cancelled","")))
>
> I need to differentiate between the two Closed and Cancelled
> Date..thats why I thought of using # (for Closed) and ~ (Cancelled).
> Any better suggestion would be helpful.
>
> Thanks to all who have helped once again
> Rashid
>

[prkhan56]

Thanks Nick,
Works great!!!