Spreadsheet for Calculation of Ra and Ri Statistics

The spreadsheet below was developed by William Wilson (WILW), a solar observer who regularly contributes his observations to the AAVSO's Solar Division. It enables the user to compare his/her R-numbers to the Ra numbers reported by the Division and to the SIDC International Index (Ri). The spreadsheet was prepared Excel 97/98/2000/2001 and will open in the PC or Mac programs in that series, since the file format is the same for all of them. It could probably be saved in Excel 5 format and run, but some formatting would be lost.

Observers should be aware that formulation of k-coefficients differs from that employed by the AAVSO. Nonetheless, the spreadsheet offers users the opportunity to compute several interesting and useful statistics for tracking their performance.

In the words of the author, ...

"This spreadsheet template will graph spot counts against the Ra for the same days each month. It also calculates the mean and standard deviation for Ri and Ra, a Pearson product-moment correlation coefficient (r), the square of the correlation, which gives the percentage of common variance between Ri and Ra, and an estimate of the individual's constant, ki.

“To use the spreadsheet, enter Ri and Ra for each day. Calculations are automatic but to get the estimated ki, you must fill the formula down in the column beside the daily counts. Either drag it down by positioning the cursor arrow at the lower right corner of the first formula cell and dragging down or highlight the first formula cell and all others to be filled and use the Fill Down command. Filling it down in advance can't be done, since you don't know how many days you will have observations before you do them. The totals for the year are at the end, after December, but partial results can't be calculated in those fields--you have to wait until the last entry is in for December to see the composites.”