[Solved] Minus values in a calculation

[kieranoh]

Hello,

I have some calculations in a spreadsheet that have worked for a long time but have recently had me scratching my head. Here is the spreadsheet example:

A B C ........................... I J K L M N O
697 157 22.53% 165.5473 150.7170 147.9089 0.58 0.33 0.71

A = 697
B = 157
I = 165.5473
J = 150.7170
K = 147.9089
M = 0.58
N = 0.33
O = 0.71

And the calcs for M, N and O are:


M =SUM(A2*(B2-I2)*(B2-I2)/(I2*(A2-I2)))
N =SUM(A2*(B2-J2)*(B2-J2)/(J2*(A2-J2)))
O =SUM(A2*(B2-K2)*(B2-K2)/(K2*(A2-K2)))

What first made me realise there was an issue is that J (and therefore N) is out performing I (and therefore M) and so the value of N should be higher than M.

When I did the sums, I realised that B2 (157) - I2 (165.5473) resulted in a negative answer and therein lies my problem. The value of M (0.58) should therefore be negative (-0.58).

The cell for M (and N and O) is set to shows negatives as red but it is displaying as a positive value. Is it a minus times a minus thing or something with the calc?

Any help will be gratefully received!

Kieran

[RoryOF]

Check your entry types by /View /Value Highlighting. Numbers are blue, text black, results of formulae green.

[kieranoh]

Rory,

Sorry, I am not clear what you mean. Could you be more explicit please ?

Kieran

Ah, sorry, now I do, just checking and will be right back......


and all of the entries involved are Green, which is correct is it not ? They are all derived from other columns and values.

[robleyd]

Can you upload a sample spreadsheet file that displays the problem you are having?
[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire or Dropbox. The link also contains information on how to anonymise your document if it contains confidential information.

[kieranoh]

Sure, here it is. I have removed some data to make it less than 128kb but the principle is the same (i.e. B2 - I2 will be negative and therefore I would expect M to be negative).

Thnaks

Kieran

[JohnSUN-Pensioner]

Dear Kieran,
perhaps my knowledge of English is not enough to understand the nature of your problem.

When I did the sums, I realised that B2 (157) - I2 (165.5473) resulted in a negative answer and therein lies my problem. The value of M (0.58) should therefore be negative (-0.58).

Indeed, 157-165 = -8. But you are powering this value in a square, is not it? Why do you expect a negative result?

[kieranoh]

Well I have to be honest and say that I did not write the calculation, rather than used what was provided to assess the probability of my results (http://false-favourites.co.uk/blog/arch ... f-winning/).

As a non math person, I assumed that A2 * (B2 - I2) would be 697 * (157 - 165.5473) would be 697 * -8.5473 = -5957.4681 ?

Then * (B2 - I2) would be * -8.5473, so -5957.4681 * -8.5473 = -5966(ish)

Then (I2*(A2-I2) 165 * (697 - 165) = 87780

and so -5966 / 87780 would be -0.6 ?

Logically it should be a minus conclusion, because there have been fewer winners than the odds expected ?

Am I being dumb ?

Kieran

[JohnSUN-Pensioner]

Yes, you are absolutely right, that is what I had in mind.

so -5957.4681 * -8.5473 = -5966(ish)

For me (and for Calc, probably, too) -5 * -8 = + 40

[kieranoh]

Hmmm the old minus and a minus. I was thinking it would be that, but in this case it is wrong!

[JohnSUN-Pensioner]

So, may be you need this variant of calculation? =$A$2*($B$2-I2)*ABS($B$2-I2)/(I2*($A$2-I2))
Upd. Please ignore this message. The formula can actually return a negative value, but it has nothing to do with ARCHIE SCORE

[kieranoh]

Could you explain perhaps ?

[Lupp]

(I am not a native speaker of English, and thus in danger to use a word now and then that may be mistaken to be meant rude or even offensive. Please be sure this is not intended.)
(I did not analyse the above linked in blog.)

1) Formulae like

Code: Select all

=SUM(A2*(B2-I2)*(B2-I2)/(I2*(A2-I2)))

are often found due to the fact that many spreadsheet users rather like chattering than seriously designing formulae. If you get an advice to use such a formula, you should suspect there's a lack of knowledge on the advisor's behalf. Having evaluated the expression

Code: Select all

A2*(B2-I2)*(B2-I2)/(I2*(A2-I2))

there is no sense in applying the SUM function on the single addend already calculated. In short:

Code: Select all

SUM(NumericValue)=NumericValue

2) "A non math person" should be aware of the fact that getting a reasonable estimation of the usability of statistic means and the significance of the results is more difficult by far than remembering a math rule from school. Advice by amateurs may not be helpful.
3) There surely is some kind of "market" for advice and statistical means concerning bets and odds pretending to help win. Everybody should take in account that the professionals making their living with the bet business know better. And a bookmaker company, turf accountant, or a seller of advice would not earn much if they betted themselves instead of milking the betters.

[kieranoh]

Actually, it is not an amateur thing or someone try to sell bets. It is a tried and trusted formula that has been around for a very long time and is used by many respected people and organisations to try and understand if they are just being lucky or have found a good method/system to be successful.

[keme]

From memory...

Archie score is a statistical indicator of "skill level" when predicting the outcome from a stochastic process. Archie score starts at 0 which means that there is no correlation, i.e. no skill involved or the process is totally random (i.e. literally unpredictable). Archie score >1 indicate that the results seem to be based on (statistically speaking) "significant skill". There is no such thing as a negative Archie score.

... then again, my memory may be totaly wrong.

[kieranoh]

Keme, you may well be correct but the fact it is showing a non negative score, that is higher (i.e. better) than more successful columns means that my spreadsheet not only looks wrong, but is wrong.

It is no big deal, I know I can just ignore it, it just bugs me!

Thanks for all of the helpful replies. I have to head out for a few hours now but will check in again later.

Thanks again.

Kieran

[kieranoh]

So, nobody can offer a solution ? Can I code to ignore a calc IF the sum of B2 - I2 is less than zero ? Is there an ELSE statement ?

[Lupp]

Nobody can give a solution to a task that was not even expressed. There is no information as far as I can see about the origin and meaning of the content in columns A, B, I, J, K. I also did not find information about what you actually wanted to achieve with your formulae.

Everybody here can tell you that ABS(Something) will suppress the sign, eg, that MAX(Somethimg;0) will return Something if positive and 0 (zero) otherwise, and that the IF function has three parameters and how they are to use: IF(Condition;IfCaseExpression;ElseCaseExpression).

This is information you also can easily get from the help. Your actual problem might be something else.

[kieranoh]

Well the information is all there Lupp if you look for it rather than just being negative (again!).

Kieran

[Lupp]

Well the information is all there Lupp if you look for it...

Well, the thread got long meanwhile, much longer probably than adequate. Nonetheless I read everything. I must have missed something, however. Being positive all the time, you surely won't mind to point me to that information, or to collect it in a way that I might understand?

[kieranoh]

The very first post showed the original values of each of the cells and the calculations being applied. A spreadsheet example was requested and was posted (albeit a cut down version) in my second post. I think all of the context etc. is covered in that.

However, I suspect JohnSUN is probably correct in saying I need to get the ABS into the calc (Thanks JohnSUN!!). I just need to figure out exactly where.

Kieran

[JohnSUN-Pensioner]

The formula, which I was strike from the discussion, was to be located in cell M2.
I removed from this formula, the SUM() function - it is not needed there (Look at the explanation of Lupp)
I concluded in ABS () the second mention of subtraction B2-I2. Now, the product will keep the sign - the product of the negative values will give a negative value.

Code: Select all

-5 * -5 = +25
-5 * ABS(-5) = -25

And finally for the two cells that are used in all three of your formulas (A2 and B2) I have applied absolute references ($A$2 and $B$2). This allows you to enter a formula in cell M2 and easily copy it to cells N2 and O2.

But I want to once again warn. Yes, with this formula you will get the red cell. But this value has nothing to do with the materials from the blog to which you referred.

[kieranoh]

Excellent. Many thanks for your time on this JohnSUN.

[kieranoh]

All works as expected now. Many thanks!

Kieran

[Lupp]

Applying statistics to business success (business may also be betting if earnings override fun) requires the definition of a measurement describing the intended success in a way to make maximising that quantity what should be achieved (following the low-level capitalist paradigm).
Concerning bets this must not be the number of wins but the amount of effective earnings, stake and costs subtracted, of course. This should be the way bookmakers look at the facts, and the reason for what they don't bet, as I suppose. Ok, in a sense they do if they offer the odds. However, they are the ones knowing the most relevant fact in betting: the actual behaviour of the humans participating.

The very first post showed the original values of each of the cells and the calculations being applied.

I was aware of this, of course. However the values don't deliver the explanation of their meaning to me. I also opened the attached .xls, but there was no explanation about the meanings of abbreviations or any intentions.
As far as I can see the only determined contribution here concerning the applied statistics is the one by "keme". However he stressed his memory being probably wrong. Did someone research about that "Archie Score"? What formula is actually given for it by professional mathematicians? I tried, maybe not hard enough, to find something about it. There was no mention in the en.wikipedia.org (which is rather rich in mathematics). Googling I found info by non-professionals concerning mathematical statistics - and in most cases only in the context of turf bets. The authors who mentioned actual statistics at least told me that the "Archie Score" was based on a Chi-squared-Test, most likely Pearson's. See: https://en.wikipedia.org/wiki/Pearson%2 ... uared_test
Looking into that article you will easily find that actually all the differences between "Observed" and "Expected" get squared. You also will see - and this should be the most interesting notion - that the formula calculating the basic quotients not is applied to sums of O-values and E-values or whatever, but is meant to return the summands of a summation. Generally the calculations for summands and the summation are not exchangeable.
Therefore I suspect the formula in M2 (e.g.) to be basically wrong in calculating a quotient based on the sums prepared in I2:K2.
Again: Did someone check this based on a trustworthy explanation of the "Archie Score" by a mathematician?

Is there

...a tried and trusted formula {proceeding (Lupp)} that has been around for a very long time...

to supppress or change arbitrarily an unwanted sign without a justification based on knowledge about the applied method of mathematical statistics? How did you research your statement about "...used by many respected people and organisations"? Was Lehman Brothers among them?

[kieranoh]

Lupp,

I am just an ordinary fella, who likes to have a bet, but believe I am better at it than many, as I consistently win, year on year. Because of that, I am continually trying to improve on my systems and one of the ways that I do that is to measure how well I am doing using SP (Starting Price), BSP (Betfair Exchange Starting Price) and the Price I was actually able to get against Archie (which as you point out is based on Chi).

When I saw a result was not logical, I tried to understand why, hence my question on here. I am not a mathematician, far from it, but I do understand the principal of beating the odds, which of course is extremely important.

All I was trying to ascertain was why something was wrong and how I could fix it.

I am happy now after the help from JohnSUN and have no interest in furthering this conversation, even if that means you feel I do not know or understand what I am doing.

Thank you and good night.

Kieran