# 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

#### Attachments

• 11.8 KB Views: 3
Last edited:

#### vletm

##### Excel Ninja
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

• 14.7 KB Views: 3

#### herclau

##### Member
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?

#### vletm

##### Excel Ninja
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

##### Member
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.
Please explain the WHY and WHAT with more details.!!!!!
My intention is to prepare an excel template

#### vletm

##### Excel Ninja
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

##### Member
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 !!!

#### vletm

##### Excel Ninja
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

##### Member
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

#### vletm

##### Excel Ninja
herclau
Many things are possible even without any formulas as below samples from Your file.

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

##### New Member
Actually somebody can explain more detailed?

#### vletm

##### Excel Ninja
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?

#### herclau

##### Member
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

#### Attachments

• 18.9 KB Views: 2
Last edited:

#### herclau

##### Member
Updated file (Simplified solution)

#### Attachments

• 17.9 KB Views: 0

#### vletm

##### Excel Ninja
Updated file
... used range (A2:B7) to get same histogram without any formulas

#### Attachments

• 13.8 KB Views: 3

#### vletm

##### Excel Ninja
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

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

#### vletm

##### Excel Ninja
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

##### Member
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

#### vletm

##### Excel Ninja
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

• 14.6 KB Views: 2

#### Peter Bartholomew

##### Well-Known Member
This is an attempt to achieve a plot of the type you describe using named (array) formulae rather than helper cells.

#### Attachments

• 21.3 KB Views: 5

#### herclau

##### Member
This is an attempt to achieve a plot of the type you describe using named (array) formulae rather than helper cells.
Genial!!!