2

I've got this Excel equation and I'm struggling to convert it into c#.

The "to the power of" and "log" parts are tripping me up.

The excel equation is as follows:

LOG((10^(PreSkillRating/400)/((-ChangeInRating/KFactor)+1)-10^(PreSkillRating/400)))*400/LOG(10)

So far I have this:

Math.Log((Math.Pow(PreSkillRating / 400, 10)) / (((ChangeInRating * -1) / KFactor) + 1) - Math.Pow((PreSkillRating / 400), 10)) * 400 / Math.Log(10)

I'm also aware that I will have to check for 0 when dividing to stop the Attempted to divide by zero error.

For example when I use the following values for each of the variables I get 1879.588002 as the answer in excel but infinity in c#.

PreSkillRating = 1600
ChangeInRating = 50
KFactor = 60

What am I doing wrong?

Bad Dub
  • 1,503
  • 2
  • 22
  • 52
  • 2
    You have `10^(PreSkillRating/400)` in your Excel formula but `Math.Pow(PreSkillRating / 400, 10)` in your C#. That's a change from `10^X` to `X^10`. – Abion47 Mar 07 '19 at 21:40
  • 1
    C# Math.Log usesnatural logarithm while excel is using decadic logarithm. So Math.Log(10) => 2.302 Excel: Log(10) => 1 – Roman Weis Mar 07 '19 at 21:43
  • Aibon and Roman, ,agree with both you. As reference, let me add for Math.Pow: https://learn.microsoft.com/en-us/dotnet/api/system.math.pow?view=netframework-4.7.2 with a good sample. | for Excel LOG(...) function: https://support.office.com/de-de/article/log-funktion-4e82f196-1ca9-4747-8fb0-6c4a3abb3280 | for Math.Log function: https://support.office.com/de-de/article/log-funktion-4e82f196-1ca9-4747-8fb0-6c4a3abb3280 | So the Math.log10(..) would be the C# equivalent to Excel LOG – Christoph Bimminger Mar 07 '19 at 21:51

2 Answers2

4

Based on earler comments and my first answer, let's summarize:

  • typecast for double division
  • wrong order of arguments for Pow
  • wrong method Math.Log(x). You can use Math.Log(x,10) or Math.Log10(x)

Try following implementation:

Math.Log10((Math.Pow(10, (double)PreSkillRating / 400)) / (((ChangeInRating * -1.0) / KFactor) + 1) - Math.Pow(10, (double)PreSkillRating / 400)) * 400 / Math.Log10(10)
Christoph Bimminger
  • 1,006
  • 7
  • 25
  • That works like a charm, thank you very much. I don't get to do equations much in C#. – Bad Dub Mar 07 '19 at 21:59
  • 1
    @BadDub As another note, what is the purpose of dividing the rest of the formula by `LOG(10)`/`Math.Log10(10)`? This would amount to `log10(10)` which is just 1, so dividing by it is redundant. – Abion47 Mar 07 '19 at 22:02
  • What was the source of your Excel formula? Probably someone already wanted to use the LN function (log e), which is equivalent to Math.Log, but didn't realize that this has to be written as LN(x) in excel? In this case, LN(10) would make some sence. However, a static value would be more useful, indeed. – Christoph Bimminger Mar 07 '19 at 22:09
1

Are your variables int values? Then you have to add a typecast. See Division in C# to get exact value

Otherwise, divisions are performed as integer divisions, which causes rounding operation for each step separately.

Christoph Bimminger
  • 1,006
  • 7
  • 25
  • Generally true, but most of the division of the given values result in whole numbers anyway, and regardless wouldn't result in a result that changes from ~1879 to infinity. Plus, regardless of the type of numerical value passed in, the return value of `Math.Pow` and `Math.Log` is `double`. – Abion47 Mar 07 '19 at 21:44
  • Yes the variables will always be ints so I've assigned them to int properties – Bad Dub Mar 07 '19 at 21:51
  • 1
    That does not fully fix it. If PreSkillRating is 1550 (and not 1600), there is a difference between PreSkillRating/400 and (double)PreSkillRating/400. But as Abion pointed out, with 1600 this does not change the result. – Christoph Bimminger Mar 07 '19 at 21:53
  • 2
    @BadDub This would cause an issue with the portion `(ChangeInRating * -1) / KFactor`, though. With the given values, this amounts to `(50 * -1) / 60`, which will be `-5/6`. As an `int` operation, this will be truncated to 0, which will screw up the rest of your operations. – Abion47 Mar 07 '19 at 21:56