Creating a formula that uses multiple logics.

[rpalmer32]

I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met.

I.E. (If associates rating is 1 [cell reference 1] and years of service is
greater than ten [cell reference 2] and they have been in job more than 3
years [cell reference 3] then return "$.20".

There will also be needed a reference to a 2 rating and a different dollar
amount. Also multiple year intervals

10-15 years 1 rating = .20 2 rating = .15
15-20 years 1 rating = .25 2 rating = .20
20+ years 1 rating = .30 2 rating = .25

I am a novice, so this is a bit over my head. Any help would be
appreciated.

Thanks.

 

[CurlerBob]

You can try this
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))
where A2 is the rating and C2 is the years of service.

If you have more ratings and year breaks, this way of doing it can become
obnoxious. An easier way would be to make a table with the dollar values and
do a lookup in the table.

 

[rpalmer32]

This is almost exactly what I want. Now I need one more condition. The
employee has to be employed at least 10 years for any of this to apply. How
do I add that reference. Thanks Again.

You can try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))
where A2 is the rating and C2 is the years of service.

If you have more ratings and year breaks, this way of doing it can become
obnoxious. An easier way would be to make a table with the dollar values and
do a lookup in the table.

I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met.

I.E. (If associates rating is 1 [cell reference 1] and years of service is
greater than ten [cell reference 2] and they have been in job more than 3
years [cell reference 3] then return "$.20".

There will also be needed a reference to a 2 rating and a different dollar
amount. Also multiple year intervals

10-15 years 1 rating = .20 2 rating = .15
15-20 years 1 rating = .25 2 rating = .20
20+ years 1 rating = .30 2 rating = .25

I am a novice, so this is a bit over my head. Any help would be
appreciated.

Thanks.

 

[CurlerBob]

The way the formula is written it will return a result of 0 for under 10
years. If you want text to show try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,"Under 10
Years"))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,"Under 10
Years"))),0))

You can replace "Under 10 Years" with any text you want.

This is almost exactly what I want. Now I need one more condition. The
employee has to be employed at least 10 years for any of this to apply. How
do I add that reference. Thanks Again.

You can try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))
where A2 is the rating and C2 is the years of service.

If you have more ratings and year breaks, this way of doing it can become
obnoxious. An easier way would be to make a table with the dollar values and
do a lookup in the table.

I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met.

I.E. (If associates rating is 1 [cell reference 1] and years of service is
greater than ten [cell reference 2] and they have been in job more than 3
years [cell reference 3] then return "$.20".

There will also be needed a reference to a 2 rating and a different dollar
amount. Also multiple year intervals

10-15 years 1 rating = .20 2 rating = .15
15-20 years 1 rating = .25 2 rating = .20
20+ years 1 rating = .30 2 rating = .25

I am a novice, so this is a bit over my head. Any help would be
appreciated.

Thanks.

 

[rpalmer32]

Thanks CurlerBob,

Yes the formula does exactly what you say it will. I however left out of my
last post that besides the 10 year requirement the employee has to be at the
top of the pay grade for 2 years. I have that date and time in another cell.
I'll write out in words what I need.

If employee X is with the company 10 years or more and has been at the top
of their paygrade for 2 or more years then they will receive the increases
that were listed.

So even though an employee may have been employed for 10 years they may only
be half way through their paygrade.

You mentioned using a table, if that is easier please advise on that topic.

Thank You.

The way the formula is written it will return a result of 0 for under 10
years. If you want text to show try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,"Under 10
Years"))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,"Under 10
Years"))),0))

You can replace "Under 10 Years" with any text you want.

This is almost exactly what I want. Now I need one more condition. The
employee has to be employed at least 10 years for any of this to apply. How
do I add that reference. Thanks Again.

You can try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))
where A2 is the rating and C2 is the years of service.

If you have more ratings and year breaks, this way of doing it can become
obnoxious. An easier way would be to make a table with the dollar values and
do a lookup in the table.

:

I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met.

I.E. (If associates rating is 1 [cell reference 1] and years of service is
greater than ten [cell reference 2] and they have been in job more than 3
years [cell reference 3] then return "$.20".

There will also be needed a reference to a 2 rating and a different dollar
amount. Also multiple year intervals

10-15 years 1 rating = .20 2 rating = .15
15-20 years 1 rating = .25 2 rating = .20
20+ years 1 rating = .30 2 rating = .25

I am a novice, so this is a bit over my head. Any help would be
appreciated.

Thanks.

 

[CurlerBob]

I see. Try this instead:

=IF(and(A2=1,D2>=2),IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(and(A2=2,D2>=2),IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))

where D2 is the number of years at the top of the pay grade.

Thanks CurlerBob,

Yes the formula does exactly what you say it will. I however left out of my
last post that besides the 10 year requirement the employee has to be at the
top of the pay grade for 2 years. I have that date and time in another cell.
I'll write out in words what I need.

If employee X is with the company 10 years or more and has been at the top
of their paygrade for 2 or more years then they will receive the increases
that were listed.

So even though an employee may have been employed for 10 years they may only
be half way through their paygrade.

You mentioned using a table, if that is easier please advise on that topic.

Thank You.

The way the formula is written it will return a result of 0 for under 10
years. If you want text to show try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,"Under 10
Years"))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,"Under 10
Years"))),0))

You can replace "Under 10 Years" with any text you want.

This is almost exactly what I want. Now I need one more condition. The
employee has to be employed at least 10 years for any of this to apply. How
do I add that reference. Thanks Again.

:

You can try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))
where A2 is the rating and C2 is the years of service.

If you have more ratings and year breaks, this way of doing it can become
obnoxious. An easier way would be to make a table with the dollar values and
do a lookup in the table.

:

I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met.

I.E. (If associates rating is 1 [cell reference 1] and years of service is
greater than ten [cell reference 2] and they have been in job more than 3
years [cell reference 3] then return "$.20".

There will also be needed a reference to a 2 rating and a different dollar
amount. Also multiple year intervals

10-15 years 1 rating = .20 2 rating = .15
15-20 years 1 rating = .25 2 rating = .20
20+ years 1 rating = .30 2 rating = .25

I am a novice, so this is a bit over my head. Any help would be
appreciated.

Thanks.

 

[rpalmer32]

Perfect. Thanks.

I see. Try this instead:

=IF(and(A2=1,D2>=2),IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(and(A2=2,D2>=2),IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))

where D2 is the number of years at the top of the pay grade.

Thanks CurlerBob,

Yes the formula does exactly what you say it will. I however left out of my
last post that besides the 10 year requirement the employee has to be at the
top of the pay grade for 2 years. I have that date and time in another cell.
I'll write out in words what I need.

If employee X is with the company 10 years or more and has been at the top
of their paygrade for 2 or more years then they will receive the increases
that were listed.

So even though an employee may have been employed for 10 years they may only
be half way through their paygrade.

You mentioned using a table, if that is easier please advise on that topic.

Thank You.

The way the formula is written it will return a result of 0 for under 10
years. If you want text to show try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,"Under 10
Years"))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,"Under 10
Years"))),0))

You can replace "Under 10 Years" with any text you want.

:

This is almost exactly what I want. Now I need one more condition. The
employee has to be employed at least 10 years for any of this to apply. How
do I add that reference. Thanks Again.

:

You can try this:
=IF(A2=1,IF(C2>=20,0.3,IF(C2>=15,0.25,IF(C2>=10,0.2,0))),IF(A2=2,IF(C2>=20,0.25,IF(C2>=15,0.2,IF(C2>=10,0.15,0))),0))
where A2 is the rating and C2 is the years of service.

If you have more ratings and year breaks, this way of doing it can become
obnoxious. An easier way would be to make a table with the dollar values and
do a lookup in the table.

:

I need to create a formula that pulls data from multiple cells. The formula
is needed to return a set amount of money after certain criteria are met.

I.E. (If associates rating is 1 [cell reference 1] and years of service is
greater than ten [cell reference 2] and they have been in job more than 3
years [cell reference 3] then return "$.20".

There will also be needed a reference to a 2 rating and a different dollar
amount. Also multiple year intervals

10-15 years 1 rating = .20 2 rating = .15
15-20 years 1 rating = .25 2 rating = .20
20+ years 1 rating = .30 2 rating = .25

I am a novice, so this is a bit over my head. Any help would be
appreciated.

Thanks.