Hello, I was wondering if there is a way to extend a formula to a given size that I can give. I am trying to create a dynamic histogram and the data I have to input varies in size. Some data is 400, others 20, and some 20000. So right now I have just extended my formulas to 20,000 which is my largest data size. However, when I try to make the graph, it takes into account the cells that are set to null. Is there any way to either extend a formula to a certain number that I can provide, or is there anyway that the graph can somehow recognize and ignore the null cells. Let me know if I can clear something up.
Extend a formula to a certain size [SOLVED]
- Thread starter alemuzzy
- Start date
Alemuzzy
Firstly, Welcome to the Chandoo.org Forums
Normally when plotting a histogram you have a fixed number of Buckets into which you assign the count of values within a range represented around the mid point of the bucket
Provided you use Dynamic Named formulas the data range can be as big / small as is required.
You can also have a variable number off Buckets into which to collate the results.
The secret here is to set the minimum bucket value based on the minimum of the data
then setup each subsequent bucket based on the required offset.
You will manually need to allow for lots of buckets to accommodate small gaps between the buckets.
You can then plot the bucket counts/histogram based on the counts of the buckets
You should allow that the buckets after the highest number show a #N/A error instead of 0 by using an If() formula in the summations
If this doesn't help can you post a sample file ?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Firstly, Welcome to the Chandoo.org Forums
Normally when plotting a histogram you have a fixed number of Buckets into which you assign the count of values within a range represented around the mid point of the bucket
Provided you use Dynamic Named formulas the data range can be as big / small as is required.
You can also have a variable number off Buckets into which to collate the results.
The secret here is to set the minimum bucket value based on the minimum of the data
then setup each subsequent bucket based on the required offset.
You will manually need to allow for lots of buckets to accommodate small gaps between the buckets.
You can then plot the bucket counts/histogram based on the counts of the buckets
You should allow that the buckets after the highest number show a #N/A error instead of 0 by using an If() formula in the summations
If this doesn't help can you post a sample file ?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
All of the calculations are correct I think. I just want to paste data in and it will give me a graph output. However, if I try to create a graph using column F (my frequencies) the bins keep going out even though there is no data there. I was wondering if there is somehow an automated way I could accomplish this so excel would recognize to ignore those empty cells and just plot the filled in ones
If what I'm asking doesn't make sense.
Try this:
Open a blank excel sheet
In column A1:A8 just type in random numbers
Highlight all of column A
Then insert 2D column graph.
Notice how it just grabbed the values that were filled in and ignored empty cells even though I highlighted the whole column. I want to do the same. However, when I highlight my column and try and create a graph since I extended my formulas to 20000. It creates a histogram with the unused cells too.
Try this:
Open a blank excel sheet
In column A1:A8 just type in random numbers
Highlight all of column A
Then insert 2D column graph.
Notice how it just grabbed the values that were filled in and ignored empty cells even though I highlighted the whole column. I want to do the same. However, when I highlight my column and try and create a graph since I extended my formulas to 20000. It creates a histogram with the unused cells too.
Thats correct
Don't choose the whole column
As I initially described used a Dynamic Named range to setup the ranges
Using Count instead of counta will only count the numbers and not the blanks or errors
eg:
xAxis: =offset(A2,,,count(A:A),1)
yAxis: =offset(D2,,,count(D:D),1)
Change columns to suit your data
Then use Sheet1!xAxis and Sheet1!yAxis in the Chart Data range dialog instead of the ranges
Don't choose the whole column
As I initially described used a Dynamic Named range to setup the ranges
Using Count instead of counta will only count the numbers and not the blanks or errors
eg:
xAxis: =offset(A2,,,count(A:A),1)
yAxis: =offset(D2,,,count(D:D),1)
Change columns to suit your data
Then use Sheet1!xAxis and Sheet1!yAxis in the Chart Data range dialog instead of the ranges