Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 18 Jan 2021 08:04:20 +0100Why these two formulas are not exactly the samehttps://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/I have 2 formulas which should give the same number but they differ by about 1%:
`=POWER(VARP(A2:L2);2)` gives 142,006944
but
`=(SUMPRODUCT((A2:L2-6,5)^2))` gives 143
Here 6,5 is the `=AVERAGE(A2:L2)`
Please see the two snippets below.
![image description](/upfiles/1610885923128568.jpg)
![image description](/upfiles/16108858886411397.jpg)Sun, 17 Jan 2021 13:22:31 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/Comment by max2 for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=288003#post-id-288003This is strange: `=POWER(SUMPRODUCT(((A2:L2-6,5)^2)/12);2)` computes the `=VARP` **but** I'm confused why there is `=POWER....; 2)` ?? The `variance =VARP` should be `E(X-EX)^2` which is exactly
`=SUMPRODUCT((A2:L2-6,5)^2)/COUNT(A2:L2)` but only after `=POWER....; 2)` this is equal to `=VARP`: in LibreOffuice,, why there must be this extra `=POWER`? for equality with `=VARP`?Sun, 17 Jan 2021 16:46:46 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=288003#post-id-288003Comment by Mike Kaganski for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=288092#post-id-288092> I do not know why it matters posting pictures of my problem, are you using a mobile phone that such a reasonably small area matters?
It is not bad to post screenshots of your problem *per se*. However, it is good when poster only posts screenshots *in addition* to also posting the editable document, so that others do not waste time re-creating sample documents to experiment with, possibly also being uncertain if the screenshot had hidden some important details like numeric precision defined in number format of the cells in question. Yes, it's rather easy to reconstruct this sample anew, but even this case would be more friendly to those who you intend to get help from, if you still provide the sample.Mon, 18 Jan 2021 08:04:20 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=288092#post-id-288092Comment by Mike Kaganski for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=288004#post-id-288004> `=POWER(SUMPRODUCT(((A2:L2-6,5)^2)/12);2)` computes the `=VARP`
No.
@Opaque: quite true!Sun, 17 Jan 2021 16:52:15 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=288004#post-id-288004Comment by Opaque for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287993#post-id-287993@Mike Kaganski - here seems to be the trap caused by standard nomenclature of variance:=σ<sup>2</sup> and assuming function `VARP()` calculates σ.Sun, 17 Jan 2021 15:31:46 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287993#post-id-287993Comment by Mike Kaganski for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287990#post-id-287990To make it slightly clearer: why do you square variance? There you have your error. Both @Lupp and @Opaque have mentioned that.Sun, 17 Jan 2021 15:02:15 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287990#post-id-287990Comment by Opaque for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287986#post-id-287986You need to compare `VARP(A2:L2)` and `=SUMPRODUCT((A2:L2-6,5)^2)/COUNT(A2:L2)` (Average of deviation squares).Sun, 17 Jan 2021 14:36:15 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287986#post-id-287986Comment by max2 for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287985#post-id-287985I do not follow your answer. I do not know why it matters posting pictures of my problem, are you using a mobile phone that such a reasonably small area matters? Also, both these formulas should be the `second central moment` and hence the same.Sun, 17 Jan 2021 14:25:09 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287985#post-id-287985Comment by Lupp for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287978#post-id-287978-1- Please stop posting such images. Lots of "area", very little information. Attach a sample document instead if not the question is explicitly about the view.
-2- Why would you expect equal results? `11.91667 * 12 <> 11.91667^2` should be obvious.Sun, 17 Jan 2021 13:53:33 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?comment=287978#post-id-287978Answer by Opaque for <p>I have 2 formulas which should give the same number but they differ by about 1%:</p>
<p><code>=POWER(VARP(A2:L2);2)</code> gives 142,006944
but</p>
<p><code>=(SUMPRODUCT((A2:L2-6,5)^2))</code> gives 143</p>
<p>Here 6,5 is the <code>=AVERAGE(A2:L2)</code></p>
<p>Please see the two snippets below.</p>
<p><img src="/upfiles/1610885923128568.jpg" alt="image description"></p>
<p><img src="/upfiles/16108858886411397.jpg" alt="image description"></p>
https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?answer=288007#post-id-288007Hello,
I did not intend to add an answer to this but due to lengthy discussion, revisited my decision.The short version of the answer to your question: **The 2 formulas calculate different values and are *not* equal.**
For a more detailed answer, check this image of pseudo formulas:
![image description](/upfiles/16109085252235464.png)
> why there must be this extra `=POWER`
It was you, who started using this. No comment stated that it is required, but all comments tried to convince you of the contrary. Therefore the only reason to start the formulas above with `POWER()` as well, is motivated by trying to clarify the original, underlying misunderstanding.
Sun, 17 Jan 2021 17:12:55 +0100https://ask.libreoffice.org/en/question/287966/why-these-two-formulas-are-not-exactly-the-same/?answer=288007#post-id-288007