[Solved] Excel Formula= If statement of Between 2 years intervals

[Jasper.Muduvhadzi]

Dear friends,

I would like to ask foo the assistance on the method of calculating the below question.
> It goes like this. Calculate Three (3) times the average monthly salary for the employee having between Five (5) and ten (10) years of service with the company.

I have tried to calculate please see the attached file for assistance. I would like to know if i am doing it wrong.

I have the feeling that i might be missing the additional operators like "=" is missing in this file. many Thanks

Status	Tuner	Average monthly salary	Final AMNT
Retired	5​	50,000​
Retired	10​	20,000​
Retired	20​	15,000​
Retired	2​	22,000​	44,000​

 

[Yousuf Shovon]

Calculate Three (3) times the average monthly salary for the employee having between Five (5) and ten (10) years of service with the company.

I have tried to calculate please see the attached file for assistance. I would like to know if i am doing it wrong.

I have the feeling that i might be missing the additional operators like "=" is missing in this file.

Hello Jasper,

Welcome to our ExcelDemy Forum! I checked out your shared file and the formula appears to calculate different values based on the conditions related to retirement status in B2 and the value in C2. However, the conditions and syntax in the formula are incorrect. That's why it returns errors as output.

Your formula:

=IF(AND(B2="Retired",C2<5),2*D2,IF(AND(B2="Retired",C2<=5<=10),3*D2,IF(AND(B2="Retired",C2>10<15),4*D2,IF(AND(B2="Retired",C2>15<20),5*D2,IF(AND(B2="Retired",C2>20<25),6*D2,IF(AND(B2="Retired",C2>25),7*D2,""))))))

In this formula, you cannot chain inequalities like this (C2<=5<=10). It's not a valid syntax. The condition should be split into two parts: (C2<=5) and (C2<=10). That's why, your first part of the formula works but others don't.

Modified Formula:

=IF(AND(B2="Retired", C2<5), 2*D2,
    IF(AND(B2="Retired", C2>=5, C2<=10), 3*D2,
        IF(AND(B2="Retired", C2>10, C2<=15), 4*D2,
            IF(AND(B2="Retired", C2>15, C2<=20), 5*D2,
                IF(AND(B2="Retired", C2>20, C2<=25), 6*D2,
                    IF(AND(B2="Retired", C2>25), 7*D2, ""))))))

• Use this formula in cell E2.

​

Note: For a complex and nested formula like this, it is better to use proper indentation for better readability.

Thus, you obtain the desired result. Read the below article, if you want to get a clear view of the IF formula with statements:

How to Use IF Function with OR and AND Statement in Excel​

I am attaching the Excel workbook here. Please, practice yourself and let us know if it works for you.

Regards,
Yousuf Shovon

 

[Jasper.Muduvhadzi]

Dear Yousuf,
Thank you for the heads up, this looks short and to the point. However, my alternative was the below but it's a quite long. On the above i now realized i was missing 3rd logical function and the allocation of the interval in terms of changing the cycle from 5,10,15,20 etc. Many Thanks

 

[Yousuf Shovon]

Dear Yousuf,
Thank you for the heads up, this looks short and to the point. However, my alternative was the below but it's a quite long. On the above i now realized i was missing 3rd logical function and the allocation of the interval in terms of changing the cycle from 5,10,15,20 etc. Many Thanks

View attachment 685

Hello Jasper,

Thank you for your feedback. I am glad to hear that you have made this work on your own. Feel free to reach out to us if you have any issues or problems related to Excel.

Regards,
Yousuf Shovon