VLOOKUP or IF Function?

[Scottmk]

HI,
I have a long column of numbers. I broke it into 10 percentile
(decile's, I believe). Next to this first decile is the # 1 ( a score
and all the way down to #10 for the last decile. Is there a formul
that can evaluate each original number and tell me what score it get
based on which deciles it falls between.
Thank

 

[Scottmk]

Please see example...thank yo

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66210

 

[Trevor Shuttleworth]

In cell F5 put: =COUNTIF($B$5:$B$50,"<="&D5)
or: =(COUNTIF($B$5:$B$50,"<="&D5))*C5

In cell F6 put:
=(COUNTIF($B$5:$B$50,"<="&D6)-COUNTIF($B$5:$B$50,"<="&D5))*C6

Drag cell F6 down through F14. You might need to modify the <= as I wasn't
quite sure what you wanted including in each range.

I think this does what you want though I've never been good with stats.

Regards

Trevor

 

[Scottmk]

Thanks for the reply Trevor, but I don't think that is what I am after.
I think that is counting the # of occurences between my given ranges.
What I need it to do is look at the data in column B and assign it
score from column C, based on which range it falls under in Colum
D......Thanks again,
Scot

 

[Ron Rosenfeld]

HI,
I have a long column of numbers. I broke it into 10 percentiles
(decile's, I believe). Next to this first decile is the # 1 ( a score)
and all the way down to #10 for the last decile. Is there a formula
that can evaluate each original number and tell me what score it gets
based on which deciles it falls between.
Thanks

I think it depends on how you have defined your deciles.

=INT(PERCENTRANK(Numbers,A2)*10)+1

where Numbers is your column of numbers, and A2 is the number whose decile you
wish to determine might be appropriate.


--ron

 

[Trevor Shuttleworth]

Scott

it's counting the number of occurrences in each range and multiplying by the
score for that range. Maybe I misunderstood what you wanted to do.

=(COUNTIF($B$5:$B$50,"<="&D6)-COUNTIF($B$5:$B$50,"<="&D5))*C6

So, in cell F6 (in my example), in the range B5 to B50, it counts everything
below the value in D6 and takes away the count of everything below the value
in D5. It then multiplies that value by the "score" in cell C6.

Dragging it down gives you the count for each range multiplied by the
"score" for that range.

But it's your question, so I guess you'll know if you've got the right
answer.

Regards

Trevor

 

[Scottmk]

Ron, that simplifies things tremedously. I tested it out on anothe
random sheet similar to the one attached. They all had equal count
except there was one less "ten" then all the other numbers and a
eleven was assigned to the Max number in the range. Any thoughts?
think the "+1" at the end of the formula may be unneccesary. Thank
for the help..You too Trevo

 

[Scottmk]

Is there any way to attach to that formula an "if statement" that say
if the data value is the max of the range not to add one. That is th
problem..It is giving it an even 10 because it is the highest and the
adding one.

Thank

 

[Ron Rosenfeld]

Is there any way to attach to that formula an "if statement" that says
if the data value is the max of the range not to add one. That is the
problem..It is giving it an even 10 because it is the highest and then
adding one.

Thanks

Try this instead:

=PERCENTRANK(Numbers,A2,1)*10+1

It seems to work OK at the extremes.

Without the +1, the sequence would be zero based rather than one based.


--ron

 

[Ron Rosenfeld]

Is there any way to attach to that formula an "if statement" that says
if the data value is the max of the range not to add one. That is the
problem..It is giving it an even 10 because it is the highest and then
adding one.

=PERCENTRANK(Numbers,A2,1)*10+1

OK, I got this to fail on a set of numbers (i.e., give an 11 at the MAX), so
use this:

=PERCENTRANK(Numbers,A2,1)*10+(A2<>MAX(Numbers))




--ron

 

[Scottmk]

PERFECT! Thanks Ron

 

[Ron Rosenfeld]

PERFECT! Thanks Ron!

You're welcome. Thank you for the feedback.


--ron

 

[Scottmk]

One more question: Using the formula already provided, how could
manipulate it so that it would do a reverse rank for some of m
columns.....for some of my data, a lower number is better.
Thanks,
Scot

 

[Ron Rosenfeld]

One more question: Using the formula already provided, how could I
manipulate it so that it would do a reverse rank for some of my
columns.....for some of my data, a lower number is better.
Thanks,
Scott

=11-formula


--ron

 

[Scottmk]

Ron,
NowI'm getting 11's and 12's. I tried switching it to
=IF(Data!I5="-","-",(11-PERCENTRANK(Data!I$4:I$47,Data!I5,1)*10+(Data!I5<>MAX(Data!I$4:I$47)))
and I also used MIN instead of MAX....but neither seemed to work. Yo
can basically ignore the IF statement, unless you see an error.
Otherwise, it is still the same formula. Thanks,
Scot

 

[Scottmk]

AHH...I got it. Thanks again for all your help Ron... As I'm sure yo
noticed it should read:

=IF(Data!I5="-","-",11-(PERCENTRANK(Data!I$4:I$47,Data!I5,1)*10+(Data!I5<>MAX(Data!I$4:I$47))))

Thanks again...I just had a parenthesis out of place