Hello,
I'm going to first explain in detail what I'm getting so that there's not confusion.
I have the following data in subsequent columns: time(s), volume(cm3), volume(m3), time/volume(s/m3).
So, as you can see, the first 2 columns are 'input type columns', in which I insert the values from experiments. The last 2 columns are calculated values based on those 2 columns. The inputs from the first 2 columns are collected from a DATA tab I set up to easily insert all experiments data.
My initial ideal was to have a DATA-tab to add all experimental data and 1 DISPLAY-tab to show the data organized and with all the plots.
To do that, I added a dropdown list with the name of the experiments and as I select one experiment, all the experimental data from the DATA-tab is displayed in the DISPLAY-tab and all the graphs and calculated values are displayed accordingly. That works just fine. However, one piece of data does not, which is the one I described above. That is because I have a table containing those 4 columns with each 42 rows (which is the max a experiment can go, but often the values are fewer than that). So, when I select, for example, experiment 1, the table displays values in 32 rows, but not in the rows below those. In fact, in the first 2 rows, which are the ones collecting data from DATA-tab, it's all 0s, the volume(m3) column it's all 0s as well due to the formula used. However, the t/V column it's all '#DIV/0!' values, because the calculation divides by 0, which is not possible, giving it this error message.
With this data, I plot t/V vs. V (which is 'time/volume(s/m3)' vs. 'volume(m3)'). In this graph, I use the linear regression Excel function to give me the equation 'y=mx+b'. As I will use the 'm' and 'b' values, on the cells below the graph I use the LINEST function to give me the 'm' and 'b' values of the range.
.
And now comes the issue: everytime I change the experiment, the amount of data changes in the first 2 columns and so the problem with the 0s and error messages described above arrises.
If I build a plot selecting the whole range of the 42 rows, my graph will plot fine, but the regression data shown in the plot will be wrong. I know that because (1) when I adjust the range manually to the cells containing valid data, the values of the equation change, and (2) the LINEST values are different from the plot when the whole range is selected, but it agrees with the range manually changed to contain only valid data.
Also, I want the LINEST function range to also change as the data changes, because it gives me an error when there's '#DIV/0!' error messages in the range, or even if there's empty cells. LINEST will only work when the valid data is in the cells in which the range is set up to.
.
I tried the Dynamic Array set up, but it didn't seem to work. Can someone help me with that?
.
Basically what a want is to:
1. Have a plot that changes its range of plotted data as the values in the corresponding range changes, to display and use for calculation values that don't include zeroes or error values;
2. Have a LINEST function to work with variable range.
I'm going to first explain in detail what I'm getting so that there's not confusion.
I have the following data in subsequent columns: time(s), volume(cm3), volume(m3), time/volume(s/m3).
So, as you can see, the first 2 columns are 'input type columns', in which I insert the values from experiments. The last 2 columns are calculated values based on those 2 columns. The inputs from the first 2 columns are collected from a DATA tab I set up to easily insert all experiments data.
My initial ideal was to have a DATA-tab to add all experimental data and 1 DISPLAY-tab to show the data organized and with all the plots.
To do that, I added a dropdown list with the name of the experiments and as I select one experiment, all the experimental data from the DATA-tab is displayed in the DISPLAY-tab and all the graphs and calculated values are displayed accordingly. That works just fine. However, one piece of data does not, which is the one I described above. That is because I have a table containing those 4 columns with each 42 rows (which is the max a experiment can go, but often the values are fewer than that). So, when I select, for example, experiment 1, the table displays values in 32 rows, but not in the rows below those. In fact, in the first 2 rows, which are the ones collecting data from DATA-tab, it's all 0s, the volume(m3) column it's all 0s as well due to the formula used. However, the t/V column it's all '#DIV/0!' values, because the calculation divides by 0, which is not possible, giving it this error message.
With this data, I plot t/V vs. V (which is 'time/volume(s/m3)' vs. 'volume(m3)'). In this graph, I use the linear regression Excel function to give me the equation 'y=mx+b'. As I will use the 'm' and 'b' values, on the cells below the graph I use the LINEST function to give me the 'm' and 'b' values of the range.
.
And now comes the issue: everytime I change the experiment, the amount of data changes in the first 2 columns and so the problem with the 0s and error messages described above arrises.
If I build a plot selecting the whole range of the 42 rows, my graph will plot fine, but the regression data shown in the plot will be wrong. I know that because (1) when I adjust the range manually to the cells containing valid data, the values of the equation change, and (2) the LINEST values are different from the plot when the whole range is selected, but it agrees with the range manually changed to contain only valid data.
Also, I want the LINEST function range to also change as the data changes, because it gives me an error when there's '#DIV/0!' error messages in the range, or even if there's empty cells. LINEST will only work when the valid data is in the cells in which the range is set up to.
.
I tried the Dynamic Array set up, but it didn't seem to work. Can someone help me with that?
.
Basically what a want is to:
1. Have a plot that changes its range of plotted data as the values in the corresponding range changes, to display and use for calculation values that don't include zeroes or error values;
2. Have a LINEST function to work with variable range.
