RANGE HELP. Horizontal Bar Graph - 5 categories, 5 Sub categories, data is RANGE

Hello all,

Not very good with graphs so I appreciate this...

All tabs represent a type of resin from biologics companies. All data needed is summarized in the "Combined" tab

The Y axis will have 5 categories/series: Chemistry that follows the tabs (Affinity, AEX, CEX, Mixed Mode, HIC) - I only have it in column B for Affinity right now.
Within that will be 5 sub categories/series of the companies (Sartorius, Cytiva, EMD, Bio-Rad, Thermo Fisher). Each one being an individual color

So 5 sections with 5 horizontal lines in it.

The x-axis will be "Particle size" and based off of the data in column C - the size. This is where I'm lost. I do not want each and every type plotted but a range based on the values. For example, Cytivia Affinity will be 33-100 but the X-axis can be 20-200.
I put a small example in columns e-k in the combined tab.

Could I just put the low and high? Yes I could but I want to keep adding to this, so dynamic if possible.

All of the information contained within this is from websites. No confidential company info.

Thank you so much in advance!

Not sure that I have interpreted your request correctly, but perhaps this proposal will at least be a base from which you can tell us what needs to be corrected:
1. Clean up the data on the Combined All sheet by removing blank rows, unmerging cells in columns A:B and then filling all cells in columns A:B.
2. Produce a pivot table with the Chemistry, Company and Bead Size fields in the Rows area and Bead Size used again in the Values area (Summarize value field by Max)
3. Produce a bar chart from the pivot table (see PT sheet)
Let us know if you have any questions.

I'm not sure I fully understand, but your picture looks like a simple stacked bar chart.

As you say, step 1 is to get the min and max for each company/chemistry pair. IMO, this will be much easier if you will fill in the blanks in the table so that each row has the complete data -- company, chemistry, bead size. I'm not sure what to make of the averages in mid table, you may want to consider moving those averages to a different column or elsewhere in the spreadsheet. Once you have a good "database" here, then you can use the MINIFS() and MAXIFS() functions to get the min and max for each company/chemistry pairing -- if your spreadsheet supports those functions. If you are using Excel 2010 as your profile suggests that does not support MINIFS()/MAXIFS(), then you will need a MIN(IF(...)) and MAX(IF(...)) array function or equivalent. I'm not very good at those kinds of array functions, but if you help us understand exactly what help you need building those array functions, we should be able to help.

Once you have a new table with min/max for each company/chemistry pair, then you can add a column with the difference between min and max.

Then create a stacked bar chart using the min and difference columns. Format the min series so that it is invisible, and you should end up with the floating bar chart you show in your picture.

If you decide you must have different colors for each company, then you will need to spread the difference column over several columns -- one column for each company, then include those columns when creating the stacked bar chart.

The tedious part is filling in the current table to make creating the conditional min/max functions easier. From there, it should be straightforward to create the summary table and then create the stacked bar chart.

Perhaps this helps:
1. Added Min and Max columns to the source data
Note that I removed row 15 as the average formula in C15 produced a circular reference error.
2. Produced a pivot table
3. Produced a pivot chart (stacked bar)
4. Formatted the Min data series to "no fill"
5. Added Data Labels (category name)
6. Manually colored the Max data series
Let us know if you have any questions.