Need help with IF function!

[dchebb]

Hi everyone! I've only recently started using if functions so I need some help with my situation.

Say I have a spreadsheet with a list of employees and their salaries. Each employee belongs to a group each with their min and max pay. So what I need to do is to use a formula to see if the salary belongs in that range, for example:

Group A (3000$ - 5000$)
Group B (4000$ - 6000$)

So the formula I'm using right now is

=IF(AND(salary>=3000,salary<=5000),"NO","YES") for Group A and for Group B just different numbers. The formula seems to be working fine, but when I change filtering it messes all up.

Is there any way to make the formula so that it knows which group an employee belongs to and use appropriate salary range? Thanks in advance

 

[CyrusTheVirus]

Hi dchebb, welcome to the forums. could you please post a sample of your data showing us what is looks like and also show how you want to look (use examples)?

 

[godsaaint]

What happens exactly when you use filters?

 

[Marcílio_Lobão]

dchebb, Good afternoon.

Salary ranges can not contain equal intervals.
If the employee receives $4500, which group does he belong to?

Do a check on the values.

Do you have one column written Group A and another written Group B?
You can have only one column written "Group" and using the formula assign A or B in this column.

Please explain your question further.
I'm sure we can help you.

 

[dchebb]

Thanks for your responses!

As was pointed out, I'm using the wrong salary ranges. For example, they should be:

Group A (3000$-5999$)
Group B (6000$-8000$)

My data is

Column A Column B Column C Column D
Employee Name Salary Employee Group Salary Review
Alex 3000 A =IF(AND(B2>=3000,salary<=5999),"NO","YES")
Katie 5000 B =IF(AND(B2>=6000,salary<=8000),"NO","YES")
David 6000 B =IF(AND(B2>=6000,salary<=8000),"NO","YES")

So I filtered groups A to B to use correct formula on each one. They seem to be working fine, however, if I have to filter by first name for example, the formulas will not be working correctly. So I was just curious if I could make the formula to determine the group and apply appropriate numbers.

 

[Marcílio_Lobão]

dchebb, Good evening.

Sure to conclude that you are doing a check if the employee's salary is within the correct group?
If the salary is in the right group the Review column receives "No", if it is wrong, it receives "Yes".
That's right?

In this case try this:

Supposing:

A2:A100 --> Employee Name
B2:B100 --> Salary Employee
C2:C100 --> Group Salary
D2:D100 --> Review

Do it:

D2 -->
=IF(OR(AND(B2>=3000, B2<=5999, C2="A"), AND(B2>=6000, B2<=8000, C2="B")), "NO", "YES")

Copy it down.

Is that what you want?
I hope it helps.

 

[dchebb]

This seems to be the answer! Thanks so much, Marcilio!

 

[Marcílio_Lobão]

Thanks for the feedback.

Glad to be of help.