Vlookup and IF

  • Thread starter Thread starter jpsabourin
  • Start date Start date
J

jpsabourin

I am unable to figure out the formula for and IF factor in Vlookup.

Example I have three delivery service, Good, Faster, Fastest. each
has a time alloted to them.

Faster is delivered in 2 hours and Fastest is delivered in 1 hour.
The problem is Good. How do I get a formula so that when Good is
entered in the delivery type cell, it will choose the the formula
according to the activated time.

i.e. If p/u at 8:am we have until 5pm to deliver, but if the call is
entered after 11:59am we have until 12noon the next day to deliver. I
cannot use different names, it needs to choose the appropriate
formula.

Thanks for your help
JP
 
Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1.5),IF(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:00"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000
 
Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1..5),IF­(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:0­0"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000










- Show quoted text -
Click to expand...

Thank you, I could have at this forever
 
Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1..5),IF­(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:0­0"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000










- Show quoted text -
Click to expand...

I am getting an error in formula, but am unable to find it, I have
notice that you have a few - where they should not be, I have removed
them, and and yet still getting the error.
Here is what I have entered.

=IF(B5="Good",IF(C5-INT(C5)<.
5,INT(C5)+TIMEVALUE("17:00:00"),INT(C5)+1.5),IF(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:00","')))
 
2 things:

First, the TIMEVALUE function needed a closing paranthesis

Second, the last argument of the IF THEN ELSE statement needed to be 2
double quotes, not a double quote and a single quote.

On the other hand, I did notice you corrected my typo by adding a double
quote after the time value for the 5pm one.

=IF(B5="Good",IF(C5-INT(C5)<0.5,INT(C5)+TIMEVALUE("17:00:00"),INT(C5)+1.5),IF(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:00"),"")))


Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date
and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1.5),IF­(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:0­0"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000










- Show quoted text -
Click to expand...

I am getting an error in formula, but am unable to find it, I have
notice that you have a few - where they should not be, I have removed
them, and and yet still getting the error.
Here is what I have entered.

=IF(B5="Good",IF(C5-INT(C5)<.
5,INT(C5)+TIMEVALUE("17:00:00"),INT(C5)+1.5),IF(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest",C5+TIMEVALUE("01:00:00","')))
 
Back
Top