SOLVED

New Contributor

Creating a formula that follows specific rules

Hi, I would like to create a formula to calculate variable earnings.

With \$4950 as the basic salary, the variable earnings are:

Less than 25% of target: 0 months of Basic Salary (BS)

Less than 50% but greater than 25% (included) of target: 0.5 to 1 month of BS

Less than 100% but greater than 50% (included) of target: 1 to 3 months of BS

Less than 150% but greater than 100% (included) of target: 3 to 6 months of BS

Greater than 150% (included) of target: 6 months of BS

A1.               B1.               C1

 % of Target Achieved Basic Salary Variable earnings 25% 4950 50% 4950 100% 4950

With the correct formula, I should be getting these amounts:

25% = \$2475

50% = \$4950

100% = \$14850

The formula should enable me to copy and paste to get the correct figures according the percentages indicated in A1.

Any help will be appreciated!

3 Replies

Re: Creating a formula that follows specific rules

@smxyc

Do you already have a new Excel version with the functions LET() or even LAMBDA().

I have the function ready, but at the moment it only works with LET().

Re: Creating a formula that follows specific rules

@dscheikey Hi, unfortunately I do not have a newer Excel version. I tested those functions in my excel and they don't prompt anything.

best response confirmed by smxyc (New Contributor)
Solution

Re: Creating a formula that follows specific rules

In the attached Excel sheet you will find my formula for interpolating with different slopes. The two ranges for the calculations are entered as aereas or are already stored in curly brackets in the formula. As it is easier for you. I have built a formula without LET(). This should work in any version of Exel.
Have a look at my file to see if you can do something with it.