Formula based on conditions

P

PT40

Hello,

Me and a co-worker have been working on an issue we are trying to figure
out. We have a basic formula but need it to apply different ratings based on
a person's tenure. Tenure will be in Column C. I will put below what we would
like to do, so if anyone has any easy suggestions, we are all ears. I say
easy because we will need to replicate it about six times for different
fields all based on tenure.

Thank you

Now if C5>=6 then we would like:

=IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280,2,IF(R5>280,1) ) ) ) )


Or if C5=5 then we would like:

=IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285,2,IF(R5>285,1) ) ) ) )


or if C5=4 then we would like:

=IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290,2,IF(R5>290,1) ) ) ) )


or if C5=3 then we would like:

=IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295,2,IF(R5>295,1) ) ) ) )


or if C5=2 then we would like:

=IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300,2,IF(R5>300,1) ) ) ) )


or if C5=1 then we would like:

=IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305,2,IF(R5>305,1) ) ) ) )


Thanks again.
 
D

David Biddulph

I assume that C5 can only be integer, and can't be less than 1?
[If these constraints don't apply, then the formula can be tweaked, but
you'll need to specify what you want for the unspecified conditions.]

=IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1
) ) ) )
 
P

PT40

Yes, that would be correct. One would be the minimum for Column C.

Let me see if this helps. One of the other conditions we have will only be
based on four months.

IF C5>=4 then we would like

=IF(H5>=36%,5,IF(H5>=31%,4,IF(H5>=24%,3,IF(H5>=22.5%,2,IF(H5>=0,1,"N/A")))))

And if C5>3,

=IF(H5>=34%,5,IF(H5>=29%,4,IF(H5>=22%,3,IF(H5>=20.5%,2,IF(H5>=0,1,"N/A")))))



And if C5>2,

=IF(H5>=32%,5,IF(H5>=27%,4,IF(H5>=20%,3,IF(H5>=18.5%,2,IF(H5>=0,1,"N/A")))))


And if C5>1,

=IF(H5>=30%,5,IF(H5>=25%,4,IF(H5>=18%,3,IF(H5>=16.5%,2,IF(H5>=0,1,"N/A")))))


Thanks for your assistance.

David Biddulph said:
I assume that C5 can only be integer, and can't be less than 1?
[If these constraints don't apply, then the formula can be tweaked, but
you'll need to specify what you want for the unspecified conditions.]

=IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1
) ) ) )
--
David Biddulph

PT40 said:
Hello,

Me and a co-worker have been working on an issue we are trying to figure
out. We have a basic formula but need it to apply different ratings based
on
a person's tenure. Tenure will be in Column C. I will put below what we
would
like to do, so if anyone has any easy suggestions, we are all ears. I say
easy because we will need to replicate it about six times for different
fields all based on tenure.

Thank you

Now if C5>=6 then we would like:

=IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280,2,IF(R5>280,1) ) ) ) )


Or if C5=5 then we would like:

=IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285,2,IF(R5>285,1) ) ) ) )


or if C5=4 then we would like:

=IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290,2,IF(R5>290,1) ) ) ) )


or if C5=3 then we would like:

=IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295,2,IF(R5>295,1) ) ) ) )


or if C5=2 then we would like:

=IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300,2,IF(R5>300,1) ) ) ) )


or if C5=1 then we would like:

=IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305,2,IF(R5>305,1) ) ) ) )


Thanks again.
Click to expand...
Click to expand...
 
P

PT40

Rather, if you wouldn't mind explaining how that formula you gave me worked.
I am at a loss, but it does seem to work. Thanks so much.

PT40 said:
Yes, that would be correct. One would be the minimum for Column C.

Let me see if this helps. One of the other conditions we have will only be
based on four months.

IF C5>=4 then we would like

=IF(H5>=36%,5,IF(H5>=31%,4,IF(H5>=24%,3,IF(H5>=22.5%,2,IF(H5>=0,1,"N/A")))))

And if C5>3,

=IF(H5>=34%,5,IF(H5>=29%,4,IF(H5>=22%,3,IF(H5>=20.5%,2,IF(H5>=0,1,"N/A")))))



And if C5>2,

=IF(H5>=32%,5,IF(H5>=27%,4,IF(H5>=20%,3,IF(H5>=18.5%,2,IF(H5>=0,1,"N/A")))))


And if C5>1,

=IF(H5>=30%,5,IF(H5>=25%,4,IF(H5>=18%,3,IF(H5>=16.5%,2,IF(H5>=0,1,"N/A")))))


Thanks for your assistance.

David Biddulph said:
I assume that C5 can only be integer, and can't be less than 1?
[If these constraints don't apply, then the formula can be tweaked, but
you'll need to specify what you want for the unspecified conditions.]

=IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1
) ) ) )
--
David Biddulph

PT40 said:
Hello,

Me and a co-worker have been working on an issue we are trying to figure
out. We have a basic formula but need it to apply different ratings based
on
a person's tenure. Tenure will be in Column C. I will put below what we
would
like to do, so if anyone has any easy suggestions, we are all ears. I say
easy because we will need to replicate it about six times for different
fields all based on tenure.

Thank you

Now if C5>=6 then we would like:

=IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280,2,IF(R5>280,1) ) ) ) )


Or if C5=5 then we would like:

=IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285,2,IF(R5>285,1) ) ) ) )


or if C5=4 then we would like:

=IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290,2,IF(R5>290,1) ) ) ) )


or if C5=3 then we would like:

=IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295,2,IF(R5>295,1) ) ) ) )


or if C5=2 then we would like:

=IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300,2,IF(R5>300,1) ) ) ) )


or if C5=1 then we would like:

=IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305,2,IF(R5>305,1) ) ) ) )


Thanks again.
Click to expand...
Click to expand...
Click to expand...
 
D

David Biddulph

The functions which I have used are IF and MIN. They are both described in
Excel help, with description of the syntax and examples of what they do,
though it looks as if you already understand the IF function.

The advice I give to anyone who doesn't understand a long formula is to
break it down into manageable chunks and look at what each part does. For
example, you can use =265-5*MIN(C5,6) and see how that responds to different
values in C5 and see how the numbers compare with those in the corresponding
place in your original formulae.

It looks as if you could deal with your new requirements in exactly the same
way.
--
David Biddulph

PT40 said:
Rather, if you wouldn't mind explaining how that formula you gave me
worked.
I am at a loss, but it does seem to work. Thanks so much.

PT40 said:
Yes, that would be correct. One would be the minimum for Column C.

Let me see if this helps. One of the other conditions we have will only
be
based on four months.

IF C5>=4 then we would like

=IF(H5>=36%,5,IF(H5>=31%,4,IF(H5>=24%,3,IF(H5>=22.5%,2,IF(H5>=0,1,"N/A")))))

And if C5>3,

=IF(H5>=34%,5,IF(H5>=29%,4,IF(H5>=22%,3,IF(H5>=20.5%,2,IF(H5>=0,1,"N/A")))))



And if C5>2,

=IF(H5>=32%,5,IF(H5>=27%,4,IF(H5>=20%,3,IF(H5>=18.5%,2,IF(H5>=0,1,"N/A")))))


And if C5>1,

=IF(H5>=30%,5,IF(H5>=25%,4,IF(H5>=18%,3,IF(H5>=16.5%,2,IF(H5>=0,1,"N/A")))))


Thanks for your assistance.

David Biddulph said:
I assume that C5 can only be integer, and can't be less than 1?
[If these constraints don't apply, then the formula can be tweaked, but
you'll need to specify what you want for the unspecified conditions.]

=IF(R5<265-5*MIN(C5,6),5,IF(R5<=275-5*MIN(C5,6),4,IF(R5<=285-5*MIN(C5,6),3,IF(R5<=310-5*MIN(C5,6),2,1
) ) ) )
--
David Biddulph

Hello,

Me and a co-worker have been working on an issue we are trying to
figure
out. We have a basic formula but need it to apply different ratings
based
on
a person's tenure. Tenure will be in Column C. I will put below what
we
would
like to do, so if anyone has any easy suggestions, we are all ears. I
say
easy because we will need to replicate it about six times for
different
fields all based on tenure.

Thank you

Now if C5>=6 then we would like:

=IF(R5<235,5,IF(R5<=245,4,IF(R5<=255,3,IF(R5<=280,2,IF(R5>280,1) ) )
) )


Or if C5=5 then we would like:

=IF(R5<240,5,IF(R5<=250,4,IF(R5<=260,3,IF(R5<=285,2,IF(R5>285,1) ) )
) )


or if C5=4 then we would like:

=IF(R5<245,5,IF(R5<=255,4,IF(R5<=265,3,IF(R5<=290,2,IF(R5>290,1) ) )
) )


or if C5=3 then we would like:

=IF(R5<250,5,IF(R5<=260,4,IF(R5<=270,3,IF(R5<=295,2,IF(R5>295,1) ) )
) )


or if C5=2 then we would like:

=IF(R5<255,5,IF(R5<=265,4,IF(R5<=275,3,IF(R5<=300,2,IF(R5>300,1) ) )
) )


or if C5=1 then we would like:

=IF(R5<260,5,IF(R5<=270,4,IF(R5<=280,3,IF(R5<=305,2,IF(R5>305,1) ) )
) )


Thanks again.
Click to expand...
Click to expand...
Click to expand...
 
Top