If Then formula for excel
 

I am trying to write a if then formula based on the category entered
into a cell. There are three categories. Category A, B, & C.

If category A is entered I want the following formula to be executed

=IF((WEEKDAY(A3)=2),A3+4,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+5,IF((WEEKDAY(A3)=5),A3+4,IF( (WEEKDAY(A3)=6),A3+4,"")))))

If category B is entered I want the following formula to be executed.

=IF((WEEKDAY(A3)=2),A3+7,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+5,IF((WEEKDAY(A3)=5),A3+5,IF( (WEEKDAY(A3)=6),A3+5,"")))))

If category C is entered I want the following formula to be executed.

=IF((WEEKDAY(A3)=2),A3+7,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+6,IF((WEEKDAY(A3)=5),A3+6,IF( (WEEKDAY(A3)=6),A3+6,"")))))

How can I accomplish this with excel?

If Then formula for excel
 

In A1 A2 and A3 put your existing formula's for A B or C respectivley
but with out the = sign.

In B1 put =IF(C1="A",INDIRECT(A1),"")
In B2 put =IF(C1="B",INDIRECT(A2),"")
In B3 put =IF(C1="C",INDIRECT(A3),"")

A B or C is entered into C1.

Any good?


---
Message posted from http://www.ExcelForum.com/

If Then formula for excel
 

Hello

Assuming A1 houses A,B,C or otherwise, try the following

=IF(WEEKDAY(A3)=1,"",IF(A1="A",VLOOKUP(WEEKDAY(A3) ,{2,4;3,6;4,5;5,4;6,4},2,0)+A3,IF(A1="B",VLOOKUP(W EEKDAY(A3),{2,7;3,6;4,5;5,5;6,5},2,0)+A3,IF(A1="C" ,VLOOKUP(WEEKDAY(A3),{2,7;3,6;4,6;5,6;6,6},2,0)+A3 ,"")))

Best Regards
Nate Oliver

If Then formula for excel
 

Use a lookup table instead:
Find an unused area and enter a table like this:
A Sun 4 6 5 4 4 Sat
B Sun 7 6 5 5 5 Sat
C Sun 7 6 6 6 6 Sat
Select the whole table and Insert-Name-Define and enter a name e.g.
adddays.
Assuming that A1 is the cell where the category is entered, then in B3
enter the formula
=VLOOKUP($A$1,adddays,Weekday(A3)+1)

Or if you don't care what happens to weekends, do it like this:
A 4 6 5 4 4
B 7 6 5 5 5
C 7 6 6 6 6
Select the whole table and Insert-Name-Define and enter a name e.g.
adddays.
Assuming that A1 is the cell where the category is entered, then in B3
enter the formula
=VLOOKUP($A$1,adddays,Weekday(A3))



CTA wrote:

I am trying to write a if then formula based on the category entered
into a cell. There are three categories. Category A, B, & C.

If category A is entered I want the following formula to be executed

=IF((WEEKDAY(A3)=2),A3+4,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+5,IF((WEEKDAY(A3)=5),A3+4,IF( (WEEKDAY(A3)=6),A3+4,"")))))

If category B is entered I want the following formula to be executed.

=IF((WEEKDAY(A3)=2),A3+7,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+5,IF((WEEKDAY(A3)=5),A3+5,IF( (WEEKDAY(A3)=6),A3+5,"")))))

If category C is entered I want the following formula to be executed.

=IF((WEEKDAY(A3)=2),A3+7,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+6,IF((WEEKDAY(A3)=5),A3+6,IF( (WEEKDAY(A3)=6),A3+6,"")))))

How can I accomplish this with excel?

If Then formula for excel
 

This basically uses Nate's approach, but used Choose rather than vlookup and
adds a few more checks.

=IF(OR(WEEKDAY(A3)=1,WEEKDAY(A3)=7,SUM(COUNTIF(A1, {"A","B","C"}))=0),"",A3+(
A1="A")*CHOOSE(WEEKDAY(A3)-1,4,6,5,4,4)+(A1="B")*CHOOSE(WEEKDAY(A3)-1,7,6,5,
5,5)+(A1="C")*CHOOSE(WEEKDAY(A3)-1,7,6,6,6,6))

--
Regards,
Tom Ogilvy



"Nate Oliver" wrote in message
...
Hello,

Assuming A1 houses A,B,C or otherwise, try the following:


=IF(WEEKDAY(A3)=1,"",IF(A1="A",VLOOKUP(WEEKDAY(A3) ,{2,4;3,6;4,5;5,4;6,4},2,0
)+A3,IF(A1="B",VLOOKUP(WEEKDAY(A3),{2,7;3,6;4,5;5, 5;6,5},2,0)+A3,IF(A1="C",V
LOOKUP(WEEKDAY(A3),{2,7;3,6;4,6;5,6;6,6},2,0)+A3," "))))

Best Regards,
Nate Oliver

If Then formula for excel
 

Try this.

=IF($A$1="A",CHOOSE(MATCH(WEEKDAY(A3),{2,3,4,5,6,6 },1),4,6,5,4,4),IF($A$1="B",CHOOSE(MATCH(WEEKDAY(A 3),{2,3,4,5,6,6},1),7,6,5,5,5),C
HOOSE(MATCH(WEEKDAY(A3),{2,3,4,5,6,6},1),7,6,6,6,6 )))+A3


Regards Robert

"CTA" wrote in message om...
I am trying to write a if then formula based on the category entered
into a cell. There are three categories. Category A, B, & C.

If category A is entered I want the following formula to be executed

=IF((WEEKDAY(A3)=2),A3+4,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+5,IF((WEEKDAY(A3)=5),A3+4,IF( (WEEKDAY(A3)=6),A3+4,"")))))

If category B is entered I want the following formula to be executed.

=IF((WEEKDAY(A3)=2),A3+7,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+5,IF((WEEKDAY(A3)=5),A3+5,IF( (WEEKDAY(A3)=6),A3+5,"")))))

If category C is entered I want the following formula to be executed.

=IF((WEEKDAY(A3)=2),A3+7,IF((WEEKDAY(A3)=3),A3+6,I F((WEEKDAY(A3)=4),A3+6,IF((WEEKDAY(A3)=5),A3+6,IF( (WEEKDAY(A3)=6),A3+6,"")))))

How can I accomplish this with excel?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004

If Then formula for excel
 

"Tom Ogilvy" wrote in message ...
This basically uses Nate's approach, but used Choose rather than vlookup and
adds a few more checks.

=IF(OR(WEEKDAY(A3)=1,WEEKDAY(A3)=7,SUM(COUNTIF(A1, {"A","B","C"}))=0),"",A3+(
A1="A")*CHOOSE(WEEKDAY(A3)-1,4,6,5,4,4)+(A1="B")*CHOOSE(WEEKDAY(A3)-1,7,6,5,
5,5)+(A1="C")*CHOOSE(WEEKDAY(A3)-1,7,6,6,6,6))

--
Regards,
Tom Ogilvy



"Nate Oliver" wrote in message
...
Hello,

Assuming A1 houses A,B,C or otherwise, try the following:


=IF(WEEKDAY(A3)=1,"",IF(A1="A",VLOOKUP(WEEKDAY(A3) ,{2,4;3,6;4,5;5,4;6,4},2,0
)+A3,IF(A1="B",VLOOKUP(WEEKDAY(A3),{2,7;3,6;4,5;5, 5;6,5},2,0)+A3,IF(A1="C",V
LOOKUP(WEEKDAY(A3),{2,7;3,6;4,6;5,6;6,6},2,0)+A3," "))))

Best Regards,
Nate Oliver

Thanks Nate, this worked.

Any suggestions on protecting the cells so the formula can't be modified/deleted?