Monthly Archives: May 2024

Uncategorized

Analyze data

In my earlier blog, I was presenting how to create a graph with minor data set below.

The trick was to remove the year text in B2.

Then activate B2:C7 range and select insert – graph.

Here we have the graph.

However, now Excel has a feature analyze data.

In my Excel, analyze data can be found under home ribbon and it is far in the right.

Activate the range B2:C7 and press analyze data.

You can scroll down and check what Excel is proposing.

Just double click.

Excel brings you a suggestion.

If we want to have a graph exactly as we had, we just ask Excel to create that.

I wrote “graph with blue line”, then I pressed the first suggestion: Show totals…

This is now the same outcome as what we created manually.

Analyze data is an artificial intelligence functionality. This example is very simple, but you can test “analyze data” functionality with your own data set.

Even though removing year in my example was not so big task, but Excel is evolving and now you get the graph faster than earlier.

Graphics

Histogram

You have a sales report. We need to make a distribution graph, a histogram, like how many values are equal or less than five, how many values are higher than five but less or equal to ten and so forth.

Our sample data is very simple, that we could see the correct results immediately.

The sentence in E3 is =COUNTIFS($B$2:$B$11;”>”&D2;$B$2:$B$11;”<=”&D3) .

The COUNTIFS is used here with two criterias. The formula is counting how many values in the data range are higher than D2 and lower or equal to D3. Bit tricky part is define the cell reference “>”&D2.

Another option is to use FREQUENCY function.  

Enter the data array B2:B11 and bin arrays D3:D6. Then write the formula in E3 and hit enter.

You will get the frequencies per bin.

The third option is to use analysis toolpak. You need to download analysis toolpak via Excel options.

Press go.

Choose analysis toolpak and press ok.

After that, I have a data analysis button far right under data view.

Write again data array and bin arrays.

Press data analysis under data view.

Select histogram and press ok.

Enter input range, bin range and output options. Activate also the chart output. Press ok.

The result.

The COUNTIFS is more complex than FREQUENCY or data analysis. Data analysis is practical once it is loaded. Excel is offering various ways to calculate and present histogram.