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.
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: | R² |
| 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:
Kommentar veröffentlichen