Microsoft Excel 2003 (full product)

Hello, I'm making a spread sheet and in cell J14 I have a value that will hold an amount. In cell K14 I would like to add an if statement that will calculate the following:

If the value is less than is less than 18, then leave it blank. If the value is between 18 and 23 ,put "coaching". If the value is between 24 and 29, put "verbal". If the value is between 30 and 35, put "written". If the value is between 36 and 38, put "90Day". If the value is 39 or greater, put "termination". Can anyone help me out with this?Thank you.

Hi, Cell J14 contains the value to be tested

Create a table in cells C1 to D6 as follows:C D 1 0 2 18 coaching 3 24 verbal 4 30 written 5 36 90Day 6 39 termination

In cell K14 enter this formula:=VLOOKUP(J14,$C$1:$D$6,2)

The text requested will show in cell K14.You can of course have the table anywhere, even in a different worksheet, just make sure

the formula points to it, e.g., =VLOOKUP(J14,Sheet3!$C$1:$D$6,2)If you prefer nested IFs then use this:

=IF(J14<18,"",IF(J14<24,"coaching",IF(J14<30,"verbal",IF(J14<36,"written",IF(J14<39,"90Day","termination")))))The VLOOKUP() version is better if you are going to use this formula in more than one place,

then if you need to change the break-points or the text, you only have to make

one change (in the table), rather than changing every formula.Regards

Ask Your Question

Weekly Poll