if statements

D

Dianne

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet ‘Sheet2’ there is a data table in range A17 – J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, “Effective Date†in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display “Not an Effective Dateâ€.
Thanks
 
G

Gary''s Student

Hi Dianne:

Do you want True to mean ANY of A18 thru A32 matching D3, or do you mean
that ALL of D18 thru A32 matches D3??
 
B

Buschwack

Dianne said:
Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet ‘Sheet2’ there is a data table in range A17 – J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, “Effective Date†in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display “Not an Effective Dateâ€.
Thanks

Hello Dianne
change the formula to read =IF(A18:A32=D3,"eff","not eff") for your first
question.
 
D

Dianne

Hi,

I made the change but I am still getting a value error when the formula runs.

Thanks,
Dianne
 
D

Daryl S

Dianne -

This will look up the value in cell D3 to see if it exists in the range
A18:A32, and if it isn't there, will display "not eff", if it is there, it
will display "eff":

=IF(isna(vlookup(D3,A18:A32,1,FALSE)),"not eff","eff")
 
D

Dianne

Daryl,

Thanks, this works kind of. I need it to do this for each cell from A18 to
A32 and as it compares each one write the eff or not eff to the cell that it
is comparing to...sorry I was not being very clear.

Thanks,
Dianne
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top