Formula to another forumula?

[57Caddy]

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

 

[David Biddulph]

=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")))))

 

[57Caddy]

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]

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.

 

[57Caddy]

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]

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?

 

[57Caddy]

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]

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?

 

[57Caddy]

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]

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.

 

[57Caddy]

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.