If then nested statement

  • Thread starter Thread starter Carter
  • Start date Start date
C

Carter

I need an if then statement that says:

If the value of A2 is between A8 and A9 then 1.50, else if A2 is between 26
and 30 then 2.00.
 
And if its not either of those conditions....leave blank? When you say
between, I'll assume you will also count if A2 is equal to A8 or A9?

=IF(AND(A2>=A8,A2<=A9),1.5,IF(AND(A2>=26,A2<=30),2,""))
 
I need an if then statement that says:
If the value of A2 is between A8 and A9 then 1.50,
else if A2 is between 26 and 30 then 2.00.
Click to expand...

That is easy. But you also cover the third case, namely when neither
condition is met. Also, "between" is ambiguous. You need to decide
if that includes the limits or not.

=if(and(A8<=A2,A2<=A9), 1.5, if(and(26<=A2,A2<=30), 2, ""))

Change "" to cover the third case.
 
I am still having problems with the formula. I am trying to set up a
worksheet to determine the cost of life insurance for employees based on a
table with multiple ranges of ages and costs. I tried the formula that you
suggested and I keep getting the same rate, no matter what age the employee
is.

Here is my table:

Age Age Rate
18 24 1.50
25 29 1.50
30 34 1.75
35 39 2.25
40 44 3.50
45 49 5.00
50 54 9.25
55 59 17.00
60 64 25.00
65 69 41.25
 
I am still having problems with the formula. I am trying to set up a
worksheet to determine the cost of life insurance for employees based on a
table with multiple ranges of ages and costs. I tried the formula that you
suggested and I keep getting the same rate, no matter what age the employee
is.

Here is my table:

Age Age Rate
18 24 1.50
25 29 1.50
30 34 1.75
35 39 2.25
40 44 3.50
45 49 5.00
50 54 9.25
55 59 17.00
60 64 25.00
65 69 41.25
Click to expand...

Looking at your problem, it is easier to give you a better answer than
something having to do with nested IF's.

You don't indicate what you want to happen if an age is not given or is out of
your range of 18-69.

Place your table in some range, e.g. H1:J11

Then use this formula (with the appropriate age in A1):

=VLOOKUP(A1,$H$1:$J$11,3)

Depending on what you want to do with out-of-range values, you could use some
if statements. Exactly what to use also depends on what the contents of A1
might be if the age is out of range.

As written, the formula will return NA if the age is less than 18 or if there
is text in A1. And it will return 41.25 for any age over 65.

Also you don't really need the second column for this formula., so you could
simplify things that way also.
--ron
 
Back
Top