Calculating formulas

Originally Posted by Vanessa Young

I am trying to calculate the amount of money that is given to an individual as a percentage of the person's salary, with the percentage varying by the number of years of employment. for example, the amount in cell A1 is the person's years of service (in this example it would be 23.422). The amount in cell a2 is the person's salary, (in this example it would be $35,440) I want the formula to calulate the following:
0 - 9.99 years of employment - 2%
10 - 19.99 years of employment - 3%
20 - 24.99 years of employment - 4%

In this example, the formula should give me $1,417.60 as my answer ($35,440 x 4% for 23.422 years of service) I would like to use this same formula for everyone, and let the formula determine which percentage to use. We can get the formula to work for anyone that has less that 9 years but after that it will not work.

Thanks for your help.

One way is to utilize a LOOKUP table. For this example of yours, a simple table can be constructed as such:

1. In (for example) Cell F1 to Cell F3, enter 0,10 and 20.
2. In Cell G1 to G3, enter 2,3 and 4 (formatted as percent)

So. with the "number of years of service" in Cell A1 and "salary" in Cell A2, you can enter this formula in Cell A3

=vlookup(A1,$F$1:$G$3,2)*A2

Hope this will help you.

Regards.