How can I add multiple charts in excel with activex
7 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
I've been writing a script that needs to add up to a hundred or so excel charts in one worksheet... I'm using activex to access excel through matlab and I'm using the Excel.ActiveSheet.ChartObjects.Add(Left,Top,Width,Height) function. One problem though, it won't seem to let me add sheets when either the 'left' or 'top' position is greater than a certain number, around 250-350. Is there anyway to fix this?
Many thanks, Mirage
7 Commenti
Eric
il 26 Ott 2012
Modificato: Eric
il 26 Ott 2012
I wonder if Matlab or Excel is choking on the superscript "2" character somehow. I was surprised to see that print out.
You can see my answer for how to easily calculate R^2 for the linear fit without the need for reading from the trendline label. The polynomial fit is a bit trickier.
-Eric
Risposta accettata
Eric
il 26 Ott 2012
Modificato: Eric
il 26 Ott 2012
Here's how you can get the linear fit parameters (in Excel) without reading text strings from the trendline labels:
K>> V = Excel.WorksheetFunction.LinEst(GetXLSheet.Range(YseriesRange),GetXLSheet.Range(XseriesRange),true,true)
V =
[ 3.0000] [ 2.0000]
[9.8546e-17] [6.1146e-16]
[ 1] [8.9509e-16]
[9.2675e+32] [ 8]
[ 742.5000] [6.4095e-30]
The slope is the (1,1) element, the offset is the (1,2) term, and the R^2 value is the (3,1) term. For my case the equation is y = 3x+2 and the R^2 is 1.
You actually can use linest() to perform polynomial fits as well, but you would probably need to have the x^2, x^3, etc. data in the worksheet as well. You might be able to create VBA arrays of these values somehow and use them, but I'm not sure.
Alternatively, you can read the values directly into Matlab and perform calculations there. You can use
Yvals = cell2mat(GetXLSheet.Range(YseriesRange).Value);
Xvals = cell2mat(GetXLSheet.Range(XseriesRange).Value);
You can then use Matlab's polyfit to perform the fitting for you. To calculate the R^2 parameter, see http://en.wikipedia.org/wiki/Coefficient_of_determination. The following code implements this. In this case y is the data vector and fit_vec is a vector of fit values.
ybar = mean(y); %Mean of the data
sst = sum( (y - ybar).^2 ); %Total sum of squares
gof.sse = sum( (y - fit_vec).^2 ); %Residual sum of squares
if sst ~= 0
gof.rsquare = 1 - gof.sse/sst;
else
gof.rsquare = NaN;
end
gof.rmse = sqrt(mean((fit_vec-y).^2));
You could then also use Matlab to create the equation strings for you as well. You could borrow code from the disp() function from http://www.mathworks.com/help/matlab/matlab_oop/a-polynomial-class.html to do that.
To summarize, I would say the best solution is to use the existing code you've got for creating the plots. This seems to work well. Then read the data into Matlab and calculate fit parameters there.
-Eric
3 Commenti
Più risposte (0)
Vedere anche
Categorie
Scopri di più su Data Import from MATLAB in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!