How do I write a formula in Excel

L

L@FUTUREALL

I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!
 
R

Ron Rosenfeld

I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!
Click to expand...

Try something like:

=VLOOKUP(lookup_value,table_array,(dt>DATE(2005,9,20))+1,range_lookup)

Note that you don't specify what you wish to happen if the date is equal to
9/20/2005. You may need to modify the formula slightly to account for this --
in the example I give, it will look in column 1.

Look in HELP for the definition of the other terms.


--ron
 
L

L@FUTUREALL

THANK YOU SO MUCH FOR ANSWERING ME BUT IT DID NOT WORK I THOUGHT THAT I WOULD
NEED TO START OUT USING AN IF FUNCTION AND NEST A V LOOKUP WITHIN THAT?
 
L

L@FUTUREALL

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!
 
R

Ron Rosenfeld

THANK YOU SO MUCH FOR ANSWERING ME BUT IT DID NOT WORK I THOUGHT THAT I WOULD
NEED TO START OUT USING AN IF FUNCTION AND NEST A V LOOKUP WITHIN THAT?
Click to expand...

If it's not working there is something else wrong with your formula, or with
your implementation of my suggestion.

(dt>DATE(2005,9,20))+1 will return a 1 if date is <=9/20/2005 and a 2 if dt is
9/2/2005. It is in the section of the formula that pertains to column number.
Click to expand...

That is what I understood you to want -- change the column number depending on
the relation of date to 20 Sep 2005. Did I misunderstand that specification?

There are certainly many different ways of constructing a formula to obtain the
same result. And it could be done with IF functions. However, since you chose
to not post your formula or your data, or how you integrated my suggestion into
your formula, or what the results were of that formula, it is not possible for
me to troubleshoot.


--ron
 
B

Biff

Please turn off caps lock!

Biff

L@FUTUREALL said:
THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS,
IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS
SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!
Click to expand...
 
J

JMB

All caps means you are shouting.

Try:
=IF(G4<DATEVALUE("9/18/2005"),VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

9/18/2005 is interpreted by Excel as 9 divided by 18 divided by 2005. Read
Excel help for more info on dates and how Excel stores date values.
 
R

Ron Rosenfeld

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!
Click to expand...


Your IF statement is not working because 9/18/2005 is not being interpreted as
a date; but rather as =9/18/2005 --> 0.000249377. Since the contents of G4
will always be greater than that number, the IF will always evaluate to TRUE.
To use dates in an unambiguous method, either use the DATE function as I did in
my suggestion, or enter the date in some cell and reference that cell.

I would still suggest:

=VLOOKUP(F4,RATE,(G4>=DATE(2005,9,18))+2)

as being simpler. Since it is now apparent that you want either column 2 or
column 3, I am adding two instead of 1 to the equality in the "column_number"
argument so as to obtain the correct numbers.

If you really, really want to use your IF statement, then try:

=IF(G4<DATE(2005,9,18),VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))





--ron
 
R

Ron Rosenfeld

All caps means you are shouting.

Try:
=IF(G4<DATEVALUE("9/18/2005"),VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

9/18/2005 is interpreted by Excel as 9 divided by 18 divided by 2005. Read
Excel help for more info on dates and how Excel stores date values.
Click to expand...

Due to international date conventions, I prefer to recommend unambiguous
solutions. So I would suggest either a reference to a cell containing the date
9/18/2005; or the DATE worksheet function; or even DATEVALUE("18 SEP 2005").

Your solution will work fine with US style dates (MDY) set as the option in the
Windows regional settings (Control Panel), but won't with other settings (e.g.
DMY).

--ron
 
L

L@FUTUREALL

Thank-You, Sorry about the all caps, it is a habit, we use them at work all
of the time, I will try your suggestions out, I am sure they will work.

Thanks,

L@Futureall
 
L

L@FUTUREALL

This If Statement worked, Thank-You So Much!! I appreciate it!!!!!!

L@Future-all
 
Top