Backward Formula

A

AL

I am trying to calculate a percentage based on a ranking system. For
example, #1 is the best. My problem:

1 out of 5 calculates to a percentage of 20%. I would like it to be a 100%
since 1 is the best.

Any help??
 
S

Stuart Douglas

use an if statement. for example
=IF(A2=1,100%,IF(A2=2,80%,IF(A2=3,60%,IF(A2=4,40%,20%))))
 
R

Rick Rothstein \(MVP - VB\)

What percentage value do you want the other the numbers to have?

What do you want to happen if there are ties?

Rick
 
G

Gary''s Student

If the rank is in A1 then:

=(6-A1)/5 will give the desired percentage (must format as percentage)
 
A

AL

AL said:
I am trying to calculate a percentage based on a ranking system. For
example, #1 is the best. My problem:

1 out of 5 calculates to a percentage of 20%. I would like it to be a 100%
since 1 is the best.

Any help??
Click to expand...

Thanks! The two posts work, but how can I calculate the percentage, when
the cell already has another formula?
 
R

Robin P

Maybe this will work for you.....
If your numbers are A1 thru A5,
Format the column next to the cells as %
Name the numbers in A1 thru a5 as "range" (say)
Next to the first cell =(max(range)-a1+1)/counta(range)

This should work with any set of continuous numbers starting at one, but
you'll need to adapt if you have decimals or non-contiguous numbers
 
A

AL

Example:

Rank Number of Chapters Percentage
1 5 100%
5 10 50%
3 4
25%

My problem is that I don't have a set number to divide the rank by.

Thanks!
 
J

Jeffrey W. Smith

Al,

Try using the Rank formula with the Order parameter set to "1".

HTH,

Jeff
 
Top