rwallacej
asked on
Excel bubble chart with most common ranges of values
I have list of outputs. Say around 100,000 rows
They give results of a calculation.
The result is between say 1 and 100 on one axis and 0 to 50 on another , with 3 or 4 decimal places
I need to know 10 "most popular " RANGES of values, the most concentrated ones,,and draw circle bubbles on these on chart around the range
They don't need to be exact values just a concentrated range
The current excel chart draws too many bubbles
I drew scatter but. It doesn't highlight most frequent area
Please advise
image.jpeg
They give results of a calculation.
The result is between say 1 and 100 on one axis and 0 to 50 on another , with 3 or 4 decimal places
I need to know 10 "most popular " RANGES of values, the most concentrated ones,,and draw circle bubbles on these on chart around the range
They don't need to be exact values just a concentrated range
The current excel chart draws too many bubbles
I drew scatter but. It doesn't highlight most frequent area
Please advise
image.jpeg
Mike in IT
Can you attach an example workbook with some of the data and the chart?
ASKER
Try attached with a 10x10 range matrix.
Range limits are found in column F:G.
The table in I2:S12 has the middle value of the ranges in first row and column, and the table values are number of counts within each range.
The 10 largest values are found in J38:J47, and if some are equal, adjusted in K38:J47, to get 10 unique values.
Same adjustment made in table J15:S24.
Row match J26:S35 and column match U15:AD24 find the rows and columns where the counts match the 10 largest corrected unique counts.
The results are in M38:N47, used to find the a and b range values in P38:Q47, for the bubble chart.
Bubble sizes are the 10 largest counts.
Bubble-chart-ranges.xlsx
Range limits are found in column F:G.
The table in I2:S12 has the middle value of the ranges in first row and column, and the table values are number of counts within each range.
The 10 largest values are found in J38:J47, and if some are equal, adjusted in K38:J47, to get 10 unique values.
Same adjustment made in table J15:S24.
Row match J26:S35 and column match U15:AD24 find the rows and columns where the counts match the 10 largest corrected unique counts.
The results are in M38:N47, used to find the a and b range values in P38:Q47, for the bubble chart.
Bubble sizes are the 10 largest counts.
Bubble-chart-ranges.xlsx
ASKER
this is beginning to look like what I'm after, at first glance
is it possible to add all the data points too as scatter?
is it possible to add all the data points too as scatter?
ASKER
I should have said thanks for good work so far, I'll look again in morning
ASKER
sum of J3 to S12 is 19404, should this be total number of records?
ASKER
like 79777 rows ?
Total number of non blank records are 19408.
If record equal min a or min b then it is not counted.
I ignored it, to have the same formula for all counts, since it was only 1, and does not matter when looking for max.
Really the first formula J3, should compare >= minimum range where the others are >.
I have changed that formula in attached file, but if copied, many counts could be wrong, not now, but potentially.
All the counts work on the entire column, so new data can be added below, without changing anything.
A scatter chart has a maximum number of values, for me (Excel 2007) it is 32,000, so that is not possible.
Formulas could extract the values to use, but calculation time will be very, very long.
In attached I have copied the data to column AH:AI, sorted on a, and removed empty cells at the bottom, and then made the scatter from that.
I suggest you do the same, and then adjust the chart ranges when new data are inserted.
VBA can do it, but I think it is out of scope for this question, and not needed, since the adjust process is simple.
Bubble-chart-ranges.xlsx
If record equal min a or min b then it is not counted.
I ignored it, to have the same formula for all counts, since it was only 1, and does not matter when looking for max.
Really the first formula J3, should compare >= minimum range where the others are >.
I have changed that formula in attached file, but if copied, many counts could be wrong, not now, but potentially.
All the counts work on the entire column, so new data can be added below, without changing anything.
A scatter chart has a maximum number of values, for me (Excel 2007) it is 32,000, so that is not possible.
Formulas could extract the values to use, but calculation time will be very, very long.
In attached I have copied the data to column AH:AI, sorted on a, and removed empty cells at the bottom, and then made the scatter from that.
I suggest you do the same, and then adjust the chart ranges when new data are inserted.
VBA can do it, but I think it is out of scope for this question, and not needed, since the adjust process is simple.
Bubble-chart-ranges.xlsx
ASKER
THANKS
I was hoping that the BUBBLE SERIES could be put on top of SCATTER, SO it would show like this, perhaps that cant be done
I was hoping that the BUBBLE SERIES could be put on top of SCATTER, SO it would show like this, perhaps that cant be done
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank, that's really unfortunate, I guess I'll just need to draw them manually
If there was an add-in even that would have been option
If there was an add-in even that would have been option