| re: Help with Excel Complex Formula
cowboyboborton wrote:[color=blue]
> 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[/color]
parts.[color=blue]
> 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[/color]
to[color=blue]
> simply playing more often -- but, it gives more weight to[/color]
[especially][color=blue]
> good results, and [it is more 'forgiving' of especially] bad results,
> such that one exceptionally bad result does not 'kill' your ranking[/color]
[],[color=blue]
> and one exceptionally good result will not give you a safe enough[/color]
lead[color=blue]
> 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,[/color]
1.9004...[color=blue]
>
>
> And then these 'natural logarithms' are converted back into
> "percentiles"...
>
> *** Log = "natural" base for logarithms -- a universal number known[/color]
as[color=blue]
> "e" = 2.718282... (second in 'fame' only to "pi" = 3.141593...) --[/color]
and[color=blue]
> "natural logarithms" are logarithms "to the base e" -- that is,[/color]
numbers[color=blue]
> expressed as powers of "e"... The base "e" is key in many[/color]
mathematical[color=blue]
> 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[/color]
Excel has both LOG and PERCENTILE functions. Have a look at the help
for syntax and usage.
| 
