Formula to another forumula?
 

We would like to have a formula that will calculate differently based on
Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals 6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently based on
Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

David, correct me if I am wrong, but that appears to be adding the fields
together. What I would like to do is have one set of ratings for someone with
one month or less, another for someone at 2 months, until we get to 6 months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently based on
Tenure of our staff. Column B will hold the Tenure. So say cell B5 equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If so, and
if you get a different result from what you expected, then please explain
what numbers you had as your inputs, what you got as the result from my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable chunks and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding the fields
together. What I would like to do is have one set of ratings for someone
with
one month or less, another for someone at 2 months, until we get to 6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently based
on
Tenure of our staff. Column B will hold the Tenure. So say cell B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

David,

Using that formula I have a banker with a 8.25% with 2 months tenure being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% = 4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If so, and
if you get a different result from what you expected, then please explain
what numbers you had as your inputs, what you got as the result from my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable chunks and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding the fields
together. What I would like to do is have one set of ratings for someone
with
one month or less, another for someone at 2 months, until we get to 6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently based
on
Tenure of our staff. Column B will hold the Tenure. So say cell B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

My formula gives a result of 1 for your input figures of 8.25% and 2. If
you've got a result of 3, then you've not got the formula right. Did you
copy and paste, or did you try to retype? Are you sure that you didn't have
a problem with the line break in the newsgroup message? If you want to copy
your formula back here, then I can look to see what you got wrong if you
can't spot it yourself.

I haven't changed the formula to tie in with your latest figures, as I
assume that you don't really have gaps (with undefined output) between
17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and
14.25%, and so on. I assume that your original specification was correct
where you'd defined the conditions as =17.5%, =16.25%, and so on?
--
David Biddulph

"57Caddy" wrote in message
...
David,

Using that formula I have a banker with a 8.25% with 2 months tenure being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% = 4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If so,
and
if you get a different result from what you expected, then please explain
what numbers you had as your inputs, what you got as the result from my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable chunks
and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding the
fields
together. What I would like to do is have one set of ratings for
someone
with
one month or less, another for someone at 2 months, until we get to 6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently
based
on
Tenure of our staff. Column B will hold the Tenure. So say cell B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

Okay, Cell C5=2, D5=8.41% and this is what I put in E5:
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(C5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

Thank you

"David Biddulph" wrote:

My formula gives a result of 1 for your input figures of 8.25% and 2. If
you've got a result of 3, then you've not got the formula right. Did you
copy and paste, or did you try to retype? Are you sure that you didn't have
a problem with the line break in the newsgroup message? If you want to copy
your formula back here, then I can look to see what you got wrong if you
can't spot it yourself.

I haven't changed the formula to tie in with your latest figures, as I
assume that you don't really have gaps (with undefined output) between
17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and
14.25%, and so on. I assume that your original specification was correct
where you'd defined the conditions as =17.5%, =16.25%, and so on?
--
David Biddulph

"57Caddy" wrote in message
...
David,

Using that formula I have a banker with a 8.25% with 2 months tenure being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% = 4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If so,
and
if you get a different result from what you expected, then please explain
what numbers you had as your inputs, what you got as the result from my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable chunks
and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding the
fields
together. What I would like to do is have one set of ratings for
someone
with
one month or less, another for someone at 2 months, until we get to 6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently
based
on
Tenure of our staff. Column B will hold the Tenure. So say cell B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I didn't
understand.

Thank you

Formula to another forumula?
 

Should be
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(D5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

You've got your C5 and D5 muddled up in one place.
It's always dangerous trying to retype a formula. Copy and paste it, and
then if you want to move source or destination cells you can do so and the
formula will automatically update to suit.

I don't know where you were getting your result of 3, though, as with your
erroneous formula I get 2?
--
David Biddulph

"57Caddy" wrote in message
...
Okay, Cell C5=2, D5=8.41% and this is what I put in E5:
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(C5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

Thank you

"David Biddulph" wrote:

My formula gives a result of 1 for your input figures of 8.25% and 2. If
you've got a result of 3, then you've not got the formula right. Did you
copy and paste, or did you try to retype? Are you sure that you didn't
have
a problem with the line break in the newsgroup message? If you want to
copy
your formula back here, then I can look to see what you got wrong if you
can't spot it yourself.

I haven't changed the formula to tie in with your latest figures, as I
assume that you don't really have gaps (with undefined output) between
17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and
14.25%, and so on. I assume that your original specification was correct
where you'd defined the conditions as =17.5%, =16.25%, and so on?
--
David Biddulph

"57Caddy" wrote in message
...
David,

Using that formula I have a banker with a 8.25% with 2 months tenure
being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% =
4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If
so,
and
if you get a different result from what you expected, then please
explain
what numbers you had as your inputs, what you got as the result from
my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable chunks
and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding the
fields
together. What I would like to do is have one set of ratings for
someone
with
one month or less, another for someone at 2 months, until we get to
6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently
based
on
Tenure of our staff. Column B will hold the Tenure. So say cell
B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I
didn't
understand.

Thank you

Formula to another forumula?
 

Yeah, I know. It is a work in progress for sure. But that did work great for
me. I hate to keep bothering you on this but need to find a way to do this
with about six other columns using varying percentages as well. Is there
somewhere that breaks this down a bit for me so I can figure the rest out?

Thanks so much for your patience and assistance.

"David Biddulph" wrote:

Should be
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(D5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

You've got your C5 and D5 muddled up in one place.
It's always dangerous trying to retype a formula. Copy and paste it, and
then if you want to move source or destination cells you can do so and the
formula will automatically update to suit.

I don't know where you were getting your result of 3, though, as with your
erroneous formula I get 2?
--
David Biddulph

"57Caddy" wrote in message
...
Okay, Cell C5=2, D5=8.41% and this is what I put in E5:
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(C5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

Thank you

"David Biddulph" wrote:

My formula gives a result of 1 for your input figures of 8.25% and 2. If
you've got a result of 3, then you've not got the formula right. Did you
copy and paste, or did you try to retype? Are you sure that you didn't
have
a problem with the line break in the newsgroup message? If you want to
copy
your formula back here, then I can look to see what you got wrong if you
can't spot it yourself.

I haven't changed the formula to tie in with your latest figures, as I
assume that you don't really have gaps (with undefined output) between
17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and
14.25%, and so on. I assume that your original specification was correct
where you'd defined the conditions as =17.5%, =16.25%, and so on?
--
David Biddulph

"57Caddy" wrote in message
...
David,

Using that formula I have a banker with a 8.25% with 2 months tenure
being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% = 3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% = 3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5, 16.25%-17.49% =
4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If
so,
and
if you get a different result from what you expected, then please
explain
what numbers you had as your inputs, what you got as the result from
my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable chunks
and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding the
fields
together. What I would like to do is have one set of ratings for
someone
with
one month or less, another for someone at 2 months, until we get to
6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate differently
based
on
Tenure of our staff. Column B will hold the Tenure. So say cell
B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I
didn't
understand.

Thank you

Formula to another forumula?
 

I can't suggest anything beyond what I suggested earlier, namely breaking
the formula into manageable chunks so that you can see for yourself what
each part is doing. You also had a suggestion for another approach at your
earlier thread, but you didn't continue that thread to explain what your
problem was with that approach.
--
David Biddulph

"57Caddy" wrote in message
...
Yeah, I know. It is a work in progress for sure. But that did work great
for
me. I hate to keep bothering you on this but need to find a way to do this
with about six other columns using varying percentages as well. Is there
somewhere that breaks this down a bit for me so I can figure the rest out?

Thanks so much for your patience and assistance.

"David Biddulph" wrote:

Should be
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(D5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

You've got your C5 and D5 muddled up in one place.
It's always dangerous trying to retype a formula. Copy and paste it, and
then if you want to move source or destination cells you can do so and
the
formula will automatically update to suit.

I don't know where you were getting your result of 3, though, as with
your
erroneous formula I get 2?
--
David Biddulph

"57Caddy" wrote in message
...
Okay, Cell C5=2, D5=8.41% and this is what I put in E5:
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(C5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

Thank you

"David Biddulph" wrote:

My formula gives a result of 1 for your input figures of 8.25% and 2.
If
you've got a result of 3, then you've not got the formula right. Did
you
copy and paste, or did you try to retype? Are you sure that you
didn't
have
a problem with the line break in the newsgroup message? If you want
to
copy
your formula back here, then I can look to see what you got wrong if
you
can't spot it yourself.

I haven't changed the formula to tie in with your latest figures, as I
assume that you don't really have gaps (with undefined output) between
17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and
14.25%, and so on. I assume that your original specification was
correct
where you'd defined the conditions as =17.5%, =16.25%, and so on?
--
David Biddulph

"57Caddy" wrote in message
...
David,

Using that formula I have a banker with a 8.25% with 2 months tenure
being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% =
3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% =
3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5,
16.25%-17.49% =
4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If
so,
and
if you get a different result from what you expected, then please
explain
what numbers you had as your inputs, what you got as the result
from
my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable
chunks
and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding
the
fields
together. What I would like to do is have one set of ratings for
someone
with
one month or less, another for someone at 2 months, until we get
to
6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate
differently
based
on
Tenure of our staff. Column B will hold the Tenure. So say
cell
B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I
didn't
understand.

Thank you

Formula to another forumula?
 

Well on that approach that person had used a table and the calculation
referred to fields that necessarily apply. In retrospect I should've queried
him further.

"David Biddulph" wrote:

I can't suggest anything beyond what I suggested earlier, namely breaking
the formula into manageable chunks so that you can see for yourself what
each part is doing. You also had a suggestion for another approach at your
earlier thread, but you didn't continue that thread to explain what your
problem was with that approach.
--
David Biddulph

"57Caddy" wrote in message
...
Yeah, I know. It is a work in progress for sure. But that did work great
for
me. I hate to keep bothering you on this but need to find a way to do this
with about six other columns using varying percentages as well. Is there
somewhere that breaks this down a bit for me so I can figure the rest out?

Thanks so much for your patience and assistance.

"David Biddulph" wrote:

Should be
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(D5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

You've got your C5 and D5 muddled up in one place.
It's always dangerous trying to retype a formula. Copy and paste it, and
then if you want to move source or destination cells you can do so and
the
formula will automatically update to suit.

I don't know where you were getting your result of 3, though, as with
your
erroneous formula I get 2?
--
David Biddulph

"57Caddy" wrote in message
...
Okay, Cell C5=2, D5=8.41% and this is what I put in E5:
=IF(D5=11.5%+1%*MEDIAN(1,C5,6),5,IF(D5=10.25%+1% *MEDIAN(1,C5,6),4,IF(D5=8.25%+1%*MEDIAN(1,C5,6),3 ,IF(C5=7%+1%*MEDIAN(1,C5,6),2,IF(D5=0,1,"N/A")))))

Thank you

"David Biddulph" wrote:

My formula gives a result of 1 for your input figures of 8.25% and 2.
If
you've got a result of 3, then you've not got the formula right. Did
you
copy and paste, or did you try to retype? Are you sure that you
didn't
have
a problem with the line break in the newsgroup message? If you want
to
copy
your formula back here, then I can look to see what you got wrong if
you
can't spot it yourself.

I haven't changed the formula to tie in with your latest figures, as I
assume that you don't really have gaps (with undefined output) between
17.49% and 17.5%, or between 16.24% and 16.25%, or between 14.24% and
14.25%, and so on. I assume that your original specification was
correct
where you'd defined the conditions as =17.5%, =16.25%, and so on?
--
David Biddulph

"57Caddy" wrote in message
...
David,

Using that formula I have a banker with a 8.25% with 2 months tenure
being
rated a 3 when they should be a one. It is totally my fault.

Let me give you exact figures.

Month One: 11.5% or higher = 5, 10.25%-11.49% = 4, 8.25%-10.24% = 3,
7.0%-8.24%=2, 6.99% = 1

Month Two: 13.5% or higher = 5, 12.25%-13.49% = 4, 10.25%-12.24% =
3,
9.0%-10.24%=2, 8.99% = 1

Month Three: 15.5% or higher = 5, 14.25%-15.49% = 4, 12.25%-14.24% =
3,
11%-12.24% = 2, 10.99% = 1.

Month four and above for this one: 17.5% or higher = 5,
16.25%-17.49% =
4,
14.25%-16.24% = 3, 13%-14.24% = 2, 12.99%=1

I appreciate your assistance.



"David Biddulph" wrote:

Adding which fields together?

Have you tried my formula and compared it with what you wanted? If
so,
and
if you get a different result from what you expected, then please
explain
what numbers you had as your inputs, what you got as the result
from
my
formula, and what you expected from your formula?

If you don't understand my formula, break it down to manageable
chunks
and
look at what each part does and compare it with what you asked for.
--
David Biddulph

"57Caddy" wrote in message
...
David, correct me if I am wrong, but that appears to be adding
the
fields
together. What I would like to do is have one set of ratings for
someone
with
one month or less, another for someone at 2 months, until we get
to
6
months
where everyone more than 6 months is rated the same.

"David Biddulph" wrote:

=IF(C5=11.5%+1%*MEDIAN(1,B5,6),5,IF(C5=10.25%+1% *MEDIAN(1,B5,6),4,IF(C5=8.25%+1%*MEDIAN(1,B5,6),3 ,IF(C5=7%+1%*MEDIAN(1,B5,6),2,IF(C5=0,1,"N/A")))))
--
David Biddulph

"57Caddy" wrote in message
...
We would like to have a formula that will calculate
differently
based
on
Tenure of our staff. Column B will hold the Tenure. So say
cell
B5
equals
6
or greater,
then

=IF(C5=17.5%,5,IF(C5=16.25%,4,IF(C5=14.25%,3,IF (C5=13%,2,IF(C5=0,1,"N/A")))))

and if B5 equals 5 then

=IF(C5=16.5%,5,IF(C5=15.25%,4,IF(C5=13.25%,3,IF (C5=12%,2,IF(C5=0,1,"N/A")))))

and so on down to B5 equaling down to 1 or less.

How do I do this? Forgive me for being kind of a hack.

Max had tried to help earlier but had a part of the formula I
didn't
understand.

Thank you