Combining IF & AND functions

[Guest]

I know that if we want to combine two conditions (upto 7) inside IF command,
we have to use nested IF, like: IF(a1>1(IF(a2<2,...),...)
Just out of curiasity, is it possible to use command "AND" inside IF for
combination of conditions?

 

[Excelenator]

Absolutely!!!

=if(and(a1>1,a1<10),a1*100,0

 

[Guest]

So, can we take this one step further? What if I wanted to combine 5 of
these into one statement? For example:
Employees get a longevity bonus based on years of employment:
1-2: 1% of previous year's gross income
3-5: 2%
6-7: 3%
8-9: 4%
10+: 5%
How do I write the formula to look at how many years the employee has
worked, then determine which bracket (based on above) that employee falls
into, then do the math based on previous year's income. So, the question
really is: How do I join 5 of those formulas you spelled out previously (if
that made any sense)?
Thanx in advance!!

 

[Pete_UK]

When you have multiple conditions it is often better to use a
different approach, particularly when the criteria can be expressed in
a simple table, like yours. Set up a table, for example in X1:Y5 like
this:

1 1%
3 2%
6 3%
8 4%
10 5%

Then assuming that your data starts in row 2, the number of years
worked is in column A, and the previous year's income is in column B,
you can use this formula (say in C2):

=IF(A2<1,B2,B2*(1+VLOOKUP(A2,X$1:Y$5,2)))

If a person has worked less than a year, this assumes no increase,
otherwise it applies the appropriate percentage increase. Copy it down
for other employees.

Hope this helps.

Pete

 

[Guest]

Actually, it does. Thank you. I had to tweak my years worked formula, to
round down, but after that, it worked.

Thank you!!

 

[Pete_UK]

You're welcome - glad it worked for you.

Pete

Actually, it does. Thank you. I had to tweak my years worked formula, to
round down, but after that, it worked.

Thank you!!








- Show quoted text -