• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automate the process of creating histograms with XY CHART!

herclau

Member
Hello,
It will be possible to arrive from arrangement 1 to 5 through formulas.
This could automate the process of creating histograms with XY CHART

61542
 

Attachments

  • ForumXYCAHRT HISTOGRAM.xlsx
    11.8 KB · Views: 4
Last edited:
herclau
Do You would like to get range A2:B6 -data to look like Your chart?
... why those 'steps'? ... I skipped those.
... why XYChart? ... anyway sample of it.
... also other chart sample
All depends from real needs.
 

Attachments

  • ForumXYCAHRT HISTOGRAM.xlsx
    14.7 KB · Views: 3
herclau
Do You would like to get range A2:B6 -data to look like Your chart?
... why those 'steps'? ... I skipped those.
... why XYChart? ... anyway sample of it.
... also other chart sample
All depends from real needs.
Why XY CHART?

Peltier Tech explains:
"... Another problem with a plain old column chart is that the bins look numerical, but the axis is categorical, and does not represent the numerical values of the bin boundaries. Unless you can do math in your head, and can force your mathematical brain to ignore what your visual brain sees, you don't know that the bins are of equal width In this case they are equal, but I've seen histograms which have unequal bin widths represented by equal width bars. another way of distorting the data .... "
The process to achieve the final arrangement to perform the XY Chart, explained by Peltier Tech, is as follows:
From array 1, -105 is removed and a shift cell up is performed. And this is array 2. Array 3 is to set all values of array 1 to zero, and put it below 2; In array 4, only the -120 row was deleted.
Finally, array 5: with the 2,3 and 4 consecutive arrays we place them in the first upper cell of the left and use the command "Sort & Filter" / "Sort Smallest to Largest"

Is it possible to execute this manual process, with the help of formulas?
 
herclau
Many things are possible to do even many ways...
but if You cannot write WHY?
and later WHAT?
... then some cases have again challenges.
 
herclau
Why & What of 'Automate the process of creating histograms with XY CHART!'
Your needed chart sample result looks same as mine result (right one) .

Even I know, that You would like to do something ... but You should explain 'what' ... with needed sample.
 
herclau
Why & What of 'Automate the process of creating histograms with XY CHART!'
Your needed chart sample result looks same as mine result (right one) .

Even I know, that You would like to do something ... but You should explain 'what' ... with needed sample.
This example is the excel file shown in # 1.
The array i is the result of using the Histogram Data Analysis tool. The other arrays are following the methodology of Peltier Tech.
My intention is to make a Template. Since I must constantly do Histograms. With the COLUMN CHART method, it is difficult to position the mean, median and mode to see the positioning of my data (SKEW).
That is why I am interested in making the steps from array 1 through 5 with formulas. This would be a great help !!!
 
herclau
No new details for me, sorry.
There are same ten values to show in a chart .. same way.
Isn't it ready? ... or what would be missing?
Even 'my files' XYchart shows same
( without 'right side line' or any specific methodology or any formulas ).
 
herclau
No new details for me, sorry.
There are same ten values to show in a chart .. same way.
Isn't it ready? ... or what would be missing?
Even 'my files' XYchart shows same
( without 'right side line' or any specific methodology or any formulas ).
Is this possible or not? And if possible: how to do it?
Everything that is needed is in that file. From array 1 to 5 through formulas !!! That is all I have asked for help.
GRACIAS
 
herclau
Many things are possible even without any formulas as below samples from Your file.
Screenshot 2019-07-23 at 12.16.01.png
What is missing? ... except someones methodology name?
If You want some formulas there ... then there could add those as many as You want ... but those formulas won't need to use.
¿Hay algo que aún no está claro?
 
WasupDude
Sample file has data, which can use at once with XYChart.
So far, there has not come any information ... why to use extra formulas?
What kind of details are You waiting for?
 
Thanks, Peter Bartholomew

For introducing me to this way of addressing solutions in Excel.
Somehow reminds me of the programming in "Hewlett-Packard HP 11C" and "Texas Instrument Programmable Calculator" years ago. Only the indirect addressing [INDIRECT ()] with the power found in these calculators is missing.

Here I present my approach to the solution requested by me above in the Forum
62151
 

Attachments

  • Histograms Using Excel XY Charts.xlsx
    18.9 KB · Views: 2
Last edited:
Updated file
... used range (A2:B7) to get same histogram without any formulas
Screenshot 2019-08-16 at 09.26.05.png
 

Attachments

  • ForumXYCAHRT HISTOGRAM.xlsx
    13.8 KB · Views: 4
herclau
How to know, what would a 'best fit' line mean?
PS. if range A2:A7 values would be eg -105, -75, -35, 15, 75,145 then there needs help column.
 
herclau
As I wrote: How to know, what would a 'best fit' line mean?
1st values are -105 & 3 ... and ... that's the 'best fit'-value is -105 & 0 ... hmm?
2nd values are -60 & 1 ... and ... that's the 'best fit'-value is -60 & 1,75 ... hmm?
What would be Your a 'best fit' -line logic?
 
herclau
As I wrote: How to know, what would a 'best fit' line mean?
1st values are -105 & 3 ... and ... that's the 'best fit'-value is -105 & 0 ... hmm?
2nd values are -60 & 1 ... and ... that's the 'best fit'-value is -60 & 1,75 ... hmm?
What would be Your a 'best fit' -line logic?
What would be Your a 'best fit' -line logic: Histogram With Normal Curve Overlay, It's what I'm looking for. But I would like to achieve with simple solution proposed above
 
herclau
Please, next time You would write at once - what would You really need?
Have You ever seen, what is 'Normal Curve Overlay' look like?
... I found a bite different looking curve
... here is one sample of it.
Your 'best fit'-line is something else ... do You know what do You need?
 

Attachments

  • ForumXYCAHRT HISTOGRAM.xlsx
    14.6 KB · Views: 3
This is an attempt to achieve a plot of the type you describe using named (array) formulae rather than helper cells.
 

Attachments

  • Histogram(PB).xlsx
    21.3 KB · Views: 6
Back
Top