Ask Your Question

Why these two formulas are not exactly the same

asked 2021-01-17 13:22:31 +0200

max2 gravatar image

updated 2021-01-17 13:24:11 +0200

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

image description

edit retag flag offensive close merge delete



-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.

Lupp gravatar imageLupp ( 2021-01-17 13:53:33 +0200 )edit

I 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.

max2 gravatar imagemax2 ( 2021-01-17 14:25:09 +0200 )edit

You need to compare VARP(A2:L2) and =SUMPRODUCT((A2:L2-6,5)^2)/COUNT(A2:L2) (Average of deviation squares).

Opaque gravatar imageOpaque ( 2021-01-17 14:36:15 +0200 )edit

To make it slightly clearer: why do you square variance? There you have your error. Both @Lupp and @Opaque have mentioned that.

Mike Kaganski gravatar imageMike Kaganski ( 2021-01-17 15:02:15 +0200 )edit

@Mike Kaganski - here seems to be the trap caused by standard nomenclature of variance:=σ2 and assuming function VARP() calculates σ.

Opaque gravatar imageOpaque ( 2021-01-17 15:31:46 +0200 )edit

This is strange: =POWER(SUMPRODUCT(((A2:L2-6,5)^2)/12);2) computes the =VARPbut 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?

max2 gravatar imagemax2 ( 2021-01-17 16:46:46 +0200 )edit

=POWER(SUMPRODUCT(((A2:L2-6,5)^2)/12);2) computes the =VARP


@Opaque: quite true!

Mike Kaganski gravatar imageMike Kaganski ( 2021-01-17 16:52:15 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2021-01-18 08:04:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-01-17 17:12:55 +0200

Opaque gravatar image

updated 2021-01-17 19:39:23 +0200


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

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-01-17 13:22:31 +0200

Seen: 63 times

Last updated: Jan 17