Donnerstag, 31. Dezember 2009

USL in excel - without the need to read

Neil Gunther created a nice small excel spreadsheet to calculate the key values sigma and kappa for his Universal Scalability Law - primarily for the use in his GCaP class. Beside some other problems with excels numerical precision and Negative Scalability Coefficients in Excel I dislike the ugly 'create a graphic, let it show a trendline and it's coefficients, add these into some cells and continue' step. Fortunately, Scott Roberts has created some google spreadsheets where he implemented a real formula (as google spreadsheets does not support trendlines and their coefficients). These are mentioned in Neils blog entry Scalability in a Spreadsheet - google style.
As this method is also available in excel, I decided to extend Neils excel by some functions to avoud the read and insert part.
sscalc-class_berx1 It is not just easier to apply, it's also more accurate as the coefficients shown in the graph are shown with small rounding.

With the formula shown here:

Don't be confused by the semicolons in the formula, depending on your language settings, excel sometimes use colon or semicolon to separate fields of the formula.
Here the detailed fields I've added:

Field H11:
Field I8:=INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE); 1)
Field I9: =INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 1);2)
Field I10: =INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 1);3)
Field I11: =INDEX(INDEX(LINEST($G8:$G14;$F8:$F14^{1,2}; FALSE;TRUE); 3);1)

I hope i used the LINEST function correct, and this might help others to reduce one step while playing with USL.

Keine Kommentare: