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