If Then formula for excel

[CTA]

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,IF((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,IF((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,IF((WEEKDAY(A3)=4),A3+6,IF((WEEKDAY(A3)=5),A3+6,IF((WEEKDAY(A3)=6),A3+6,"")))))

How can I accomplish this with excel?

 

[ianripping]

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?

 

[Guest]

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",VLOOKUP(WEEKDAY(A3),{2,7;3,6;4,6;5,6;6,6},2,0)+A3,"")))

Best Regards
Nate Oliver

 

[JWolf]

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))

 

[Tom Ogilvy]

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))

 

[Robert McCurdy]

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(A3),{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]

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))

Thanks Nate, this worked.

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