Excel and histogram data

[Fincer]

Hi,

I'm trying to create a similar histrogram in Excel as seen in CloudCompare (Edit -> Scalar Fields -> Compute stat.params -> Distribution: Gauss).

Excel histrogram so far:



Same histogram in CloudCompare:



Excel histogram does not show exactly same data as seen in CloudCompare. There is some information missing, especially the Gauss Mean Value, Standard Deviation and grey-colored non-linear curve. I'm especially interested in these three things. What Excel functions and exported CSV data rows should I use in Excel to calculate/represent them the way CloudCompare does? I basically understand the meaning of all exported rows (Class, Value, Class start, Class end) but just can't get the math right. I know Excel has STDEV.S, AVERAGE, NORM.DIST etc. functions but I need to know what data I'd use as an input if I used those Excel functions + What are the correct function syntaxes I should use in Excel... I'm quite lost with this now.

(Basically, I am looking for some basic mathematical information behind the seen histograms because I'll likely show this data in a public presentation. But just with an simple image as a source, I can't get these mathematical facts I need.)

I'm using Excel 2010.

Any help appreciated. Thanks :)

[daniel]

Ah, generally people wants to export to Excel to do different things than CC, not the exact same thing ;)

To compute the same values as CloudCompare you will need the actual scalars values of the cloud (and not the histogram bins). If there are not too many points in the cloud you can import the point cloud in Excel as an ASCII file (the number of lines Excel can load depends on its version).

Once you have loaded the scalar values, here are the informations you need:

• the Gauss mean value is also named 'Normal distribution mean value' and is in fact the classical mean/average of a set of values. You can use the 'AVERAGE' function of Excel
• Same thing for the standard deviation, you can use the 'STDEV' function of Excel
• And last but not least, the curve is the representation of the Gauss/Normal distribution (see https://en.wikipedia.org/wiki/Normal_distribution). You can draw it by generating a set of (X,Y) couples where X spans between the min and max range of the histogram and Y equals:

  Code: Select all

  Y = N * EXP(-POW(X-AVERAGE(...);2)/(2*POW(STDEV(...);2))
  

  N is a normalization parameter. You can set it as you wish as the scales of both graphs have nothing in common. For instance you can use the theoretical normalization term (see Wikipedia) and multiply it by the total number of points for instance.