im trying to write a formula based on contents of column A. there are five
criteria. a container must be retrieved 30 days after a drop-off date. there
are four exceptions. for two companies, its the drop-off date plus 3 days.
for two other companies, its the drop-off plus 5 days. for all other
companies, its the drop-off date plus 30 days. the company names are in
column A. the drop-off date is in column B. the formula i wrote that adds the
correct day count for a specific 30 day company is as follows:
=IF(a15="macys",B15+30)
i though i could nest 7 criteria but its not working. any help would be
appreciated. i need to add the second company that gets 30 days and the two
companies that get 3 days each from drop-off date. then the final criteria:
all other companies that dont meet the 3 day and 30 day criteria get just
column b date+5 days.
thanks in advance.

Create a table with your "exception" companies, giving Company name and
period; in my exaple below, table is columnsEF, rows 1 to 4. This allows you
to easily extend for new "exceptions".

In column C:

=IF(ISERROR(VLOOKUP(A1,$E$1:$F$4,2,0)),B1+30,B1+VL OOKUP(A1,$E$1:$F$4,2,0))

HTH

"Outbacker" wrote:

im trying to write a formula based on contents of column A. there are five
criteria. a container must be retrieved 30 days after a drop-off date. there
are four exceptions. for two companies, its the drop-off date plus 3 days.
for two other companies, its the drop-off plus 5 days. for all other
companies, its the drop-off date plus 30 days. the company names are in
column A. the drop-off date is in column B. the formula i wrote that adds the
correct day count for a specific 30 day company is as follows:
=IF(a15="macys",B15+30)
i though i could nest 7 criteria but its not working. any help would be
appreciated. i need to add the second company that gets 30 days and the two
companies that get 3 days each from drop-off date. then the final criteria:
all other companies that dont meet the 3 day and 30 day criteria get just
column b date+5 days.
thanks in advance.

=IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1= "company3",A1="company4"),B1+5,B1+30))

You will have to replace the names of the 4 companies with the actual
names, company1 and company2 are the +3 days, 3 & 4 plus 5 days the
rest plus 30

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=574130

appreciate the help guys thanks. on yours dav, i'd like to know what i can
add to your formula to return an empty cell when there is no date in column
B. right now its returning a 1900 date. thanks again in advance.

"Dav" wrote:


=IF(OR(A1="company1",A1="company2"),B1+3,IF(OR(A1= "company3",A1="company4"),B1+5,B1+30))

You will have to replace the names of the 4 companies with the actual
names, company1 and company2 are the +3 days, 3 & 4 plus 5 days the
rest plus 30

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=574130

Try amending Dav's formula as follows:

=IF(B1="","",IF(OR(A1="company1",A1="company2"),B1 +3,IF(OR(A1=
"company3",A1="company4"),B1+5,B1+30)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=574130