Creating a formula that follows specific rules

[smxyc]

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!

[#2]

@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().

[#3]

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

[#4 · Accepted solution]

@smxyc 

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.