SOLVED

Creating a formula that follows specific rules

New Contributor

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 AchievedBasic SalaryVariable 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

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

@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

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

 

dscheikey_0-1664283062176.png