Need help making histogram

[achelp]

I am running a test on how long a faucet runs when people wash their hands with different kinds of soap. I have 3 different sheets for 3 different soaps with the following columns:

-faucet time

-quantity of pumps of soap



How can I make a histogram with this data? I am a novice with excel.

[MrShorty]

As a novice, one of the first things I might recommend for something like this is to get your data into a good "database" -- rather than 3 different sheets for 3 different soaps, put all of the data into a single sheet in a good database format. One record for each "trial"/"sample" and all of the needed "fields" for each trial -- id#, soap type, faucet time, quantity of soap, anything else.

With a good database, the frequency data should be easily computed using the COUNTIFS() function https://support.office.com/en-us/art...c-aa8c2a866842 (another good thing to learn as a novice is how to search and understand Excel's help files). Of course, the COUNTIFS() function does not require that all of the data be in a single database -- it can also work just find for the 3 sub-databases that you currently have. It depends in part on how you want to work with Excel, but there are frequently times when it is preferable to have all of the data in a central database.

In addition to the COUNTIFS() function, Excel has some good pivot table tools that may be useful for summarizing the data. I am not an expert on pivot tables, but you may want to look into learning about them as well. https://www.excel-easy.com/data-****...ot-tables.html

Once you have the frequency data summarized in a table(s) with the COUNTIFS() function, then creating the histogram(s) should be as simple as adding a histogram(s) or column chart(s) (depending on which chart type you prefer to build your histogram):
column charts: https://support.office.com/en-us/art...0-E9AB353C4C00
histogram chart: https://support.office.com/en-us/art...d-80f17ff2f4e8
Note that the built in histogram chart in 2016 may not be able to "filter" the data by soap type and/or # of pumps. It appears to me that you are trying to draw correlations and make inferences about how these three variables are interrelated, and the built in histogram chart may not be able to help. In that case, you will have to use a column chart to build your histogram.

From that overview of the problem, what do you need help with?

[achelp]

Thank you for the detailed response. Before seeing your response I found a helpful tutorial on histograms in excel. I successfully made the histogram but only for one soap all as one set of data. Is there any way to overlay the multiple sets of data? I originally made a simple bar chart showing the volume of water used (based on the time data and the flow rate of the faucet) but would like a histogram with the x axis in intervals of seconds.

[MrShorty]

Short but useless answer -- yes, it is possible to "overlay" (or otherwise display different histograms in a way that will allow you to compare them) multiple sets of data. I find that a lot of charting work is really about what is going on in the spreadsheet, so I cannot make any specific recommendations (beyond what I mentioned previously) without seeing what you have done in the spreadsheet, and what data your chart is using in the spreadsheet (your pictures only show charts).

For example, having the x axis show seconds requires that you have a column of seconds in your spreadsheet, then tell the chart to use that column as the category x axis data. Do you have that column, or do you still need to generate that column? Are you familiar with the Select Source Data dialog (wherever it is found in 2016) so that you can manipulate the source data for the chart?

It might help to know what tutorial you followed to generate the pictures you've attached. It would also help to upload a sample spreadsheet (with dummy data, if you are worried about intellectual property issues) to the forum so we can see what you are doing.

[achelp]

Yes I made that column, I used the data analysis toolpak. I am not familiar with
Selecting Source Data.


It won't let me post a link to the tutorial but it was How to Make a Histogram in Excel with the website TrumpExcel

I uploaded a sample spreadsheet. Thank you for your help.

[MrShorty]

As I suggested, I think the first thing I would do is to combine all three tabs into a single good database -- adding soap # as a field. You can use Get and Transform, if you want -- it is a useful utility to learn how to use. At the same time, you only have 3 soaps, so it should be easy enough to manually copy and paste to get the tables merged together.

The power of putting everything into a single database is illustrated in how it simplifies your average functions in K5:K13. Right now, you have to manually choose which cells to include in each average. With all of the data in a single database, a single AVERAGEIFS() function (paying attention to relative and absolute references) would compute all of these averages. Something like =AVERAGEIFS(water volume range,soap # range,H5,pump qty range, I5) (note that you need to unmerge H5 and repeat the soap # for each row -- most experienced users of Excel around here don't like merged cells anyway). Changing this to an AVERAGEIFS() function is good practice, because the AVERAGEIFS() function is very similar to the COUNTIFS() function that I would use for the histogram count/frequency data.

AVERAGEIFS() help file: https://support.office.com/en-us/art...8-f7c5c3001690
COUNTIFS() help file: https://support.office.com/en-us/art...c-aa8c2a866842

Assuming column M is your "bin" range for faucet time (and that you have combined everything into a single database), I would construct the table with the frequency counts like this:

1) Enter soap numbers in N1:P1
2) Count the frequency data using COUNTIFS() =COUNTIFS(faucet time range,">="&$M2,faucet time range,"<"&$M3,soap # range, N$1) [corrected] for all but the last "overflow" bin, where you remove the "faucet time range is less than next row of column M" arguments. Note the mix of relative and absolute references that I used for easy copying. Then copy down and across.
2a) Adding other variables, like pump qty should be as simple as adding additional columns and additional criteria to the COUNTIFS().
3) Build the histogram(s)/column chart(s) from this data. Details depend on exactly what you want to do here -- separate histograms for each criteria? Try to combine it all into a single chart? or whatever.

I will also note that, if you put all of your data into a single database, a pivot table may also do these summary steps faster and more efficiently. I am not very good with pivot tables, but you may want to explore that option as well: https://www.excel-easy.com/data-****...ot-tables.html

[achelp]

I am very confused about the countifs function. If I just make the histogram for one soap, what should the function look like?

[MrShorty]

I made a mistake in my previous COUNTIFS() -- I mistakenly put an extra count range at the front. Did you read the help file and understand it? Anything specific you are having trouble understanding?

If I use your sample file from post 5, assume that column M has the "faucet time" bins that you want to use, and wanted to count how many entries for each bin for 1 pump, I would:
1) Enter 1 in N1
2) In N2, enter =COUNTIFS($C$2:$C$250,">="&$N2,$C$2:$C$250,"<"&$N3,$D$2:$D$250,N$1). Copy down.

Note that the only difference between this COUNTIFS() and one that is on a larger database where you also have a soap# field is the addition of an additional criteria_range and criteria arguments.

If I decided to use a pivot table instead, I would:
1) Select columns A-F and insert a pivot table.
2) Instruct the pivot table to use "faucet time" as row labels, pump quantity as column labels, and count of faucet times as the value field settings.
3) Right click on the row labels "faucet time" column and group the entries starting at 0 to 41 by 1 or 2 or whatever bin size you want.

[achelp]

entering 1 in N1 and =COUNTIFS($C$2:$C$250,">="&$N2,$C$2:$C$250,"<"&$N3,$D$2:$D$250,N$1) in N2 gives an error: there are one or more circular references where a formula refers to its own cell either directly or indirectly" and just enters 0 for all of the values. I tried changing the N's to M's but that still gives all zeroes

[MrShorty]

Good catch. Did you change all of the Ns to Ms? The N2 and N3 need to be M2 and M3, but the N$1 reference still needs to be to N$1 (I expect that M1 is blank/empty, which will cause the COUNTIFS() to return 0).

[achelp]

Thanks, I got it now!