Help with Excel Complex Formula

[cowboyboborton]

Looking for some help here. I've tried to solve this, but I just
can't. What I need to know is what formula to use in an excel
calculation to complete the following calculation. It's in two parts.
If first finds a natural logarithm, then converts it to a percentile.
Even if I had to do it in two steps on the spreadsheet, I can't
determine the calculations.
************************************************** *
Logarithm System
The "natural logarithm" [player] rankings system gives no advantage to
simply playing more often -- but, it gives more weight to [especially]
good results, and [it is more 'forgiving' of especially] bad results,
such that one exceptionally bad result does not 'kill' your ranking [],
and one exceptionally good result will not give you a safe enough lead
that you can then just 'sit on'...

Calculation details:

Where "X" is [a player's] finishing place out of "N" players, each []
individual tournament result is valued at:

*** Log ( (N + 1) / X )

....averaged over his number of plays...for example, with 300-player
tournaments, a player finishing 1st, 300th, 300th would rate 1.9046,
just slightly better than a player finishing 45th every time, 1.9004...
And then these 'natural logarithms' are converted back into
"percentiles"...

*** Log = "natural" base for logarithms -- a universal number known as
"e" = 2.718282... (second in 'fame' only to "pi" = 3.141593...) -- and
"natural logarithms" are logarithms "to the base e" -- that is, numbers
expressed as powers of "e"... The base "e" is key in many mathematical
applications.

Converting logarithms back to percentiles

To convert the natural log score back to percentile, you need to use
this formula:

(1 - exp (-L) ) * 100

The variable L is the average of all the natural log score. The 'exp'
means the inverse of natural log.

For example, these are log scores from 3 different tournaments: 1.5,
2.0, and 1.0, so the average is 1.5.

Then plug that number into the above formula:

= (1 - exp ( -1.5 ) )* 100
= (1 - 0.22313016 ) * 100
= 77.686984

[Tanis]

cowboyboborton wrote:

Looking for some help here. I've tried to solve this, but I just
can't. What I need to know is what formula to use in an excel
calculation to complete the following calculation. It's in two parts. If first finds a natural logarithm, then converts it to a percentile.
Even if I had to do it in two steps on the spreadsheet, I can't
determine the calculations.
************************************************** *
Logarithm System
The "natural logarithm" [player] rankings system gives no advantage to simply playing more often -- but, it gives more weight to [especially] good results, and [it is more 'forgiving' of especially] bad results,
such that one exceptionally bad result does not 'kill' your ranking [], and one exceptionally good result will not give you a safe enough lead that you can then just 'sit on'...

Calculation details:

Where "X" is [a player's] finishing place out of "N" players, each []
individual tournament result is valued at:

*** Log ( (N + 1) / X )

...averaged over his number of plays...for example, with 300-player
tournaments, a player finishing 1st, 300th, 300th would rate 1.9046,
just slightly better than a player finishing 45th every time, 1.9004...

And then these 'natural logarithms' are converted back into
"percentiles"...

*** Log = "natural" base for logarithms -- a universal number known as "e" = 2.718282... (second in 'fame' only to "pi" = 3.141593...) -- and "natural logarithms" are logarithms "to the base e" -- that is, numbers expressed as powers of "e"... The base "e" is key in many mathematical applications.

Converting logarithms back to percentiles

To convert the natural log score back to percentile, you need to use
this formula:

(1 - exp (-L) ) * 100

The variable L is the average of all the natural log score. The 'exp'
means the inverse of natural log.

For example, these are log scores from 3 different tournaments: 1.5,
2.0, and 1.0, so the average is 1.5.

Then plug that number into the above formula:

= (1 - exp ( -1.5 ) )* 100
= (1 - 0.22313016 ) * 100
= 77.686984

Excel has both LOG and PERCENTILE functions. Have a look at the help
for syntax and usage.

[redbelly]

The Excel functions you need are LN, AVERAGE, and EXP. (LOG is used
for base-10 logarithms in Excel)

For example, if column B has a player's finishing place, and column C
has the number of players in that tournament.
For cell C2 to have the natural log ranking based on the contents of A2
and B2, you would have the following formulat in C2:

=LN((B2+1)/A2)

Mark