So I am taking this radio astronomy class and for the lab we get a ton of data. We have 2 scans of the sun, one in each direction, and each scan has 51 points and each point has 9 to 10 data points. On top of that I have to take averages for each of these 51 points for both scans, which means 101 averages and then 101 standard deviations and RMS uncertainties.
So my question, is there a way that I can set up excel to populate all of averages by itself without having to go in an manually set up each one? In the data sheet itself, each set of points I need to average is separated by a blank cell, and I imagine this would help but I dont have enough programming experience to make something that can do this.
Any help would be much appreciated, especially because there are going to be 4 other labs like this throughout the semester.
If you set up a single cell with whatever formula accessing other cells, and copy and paste it to other cells, the accessed ‘other’ cells are shifted by the difference between the first cell and the pasted cell. Damn, why do I always end up talking like a damn programmer.
Let’s say for the first point the data point is in A1, the deviation from mean in A2, and the mean value for all of the data sets is in some other point fuck knows where, say AA2, after all of the data columns. You set AA2 to be a SUM with the range over the full data points, using the sigma button at the top. Set A2 to “=$AA$2-A1”. Now, copy A2 to the clipboard, highlight B2 through WHEREEVER2, right click paste. All of the cells will be set to “=$AA$2-WHATEVER_IS_ABOVEIT”
Just know how the cell addressing changes when copy and pasted, and also how to anchor the cells with $'s, and you can do some neat stuff.
I understand how the filld down and pasting works, it just doesnt help much with how the data is set up. Although I was thinking and I believe I have a way I can make one formula and have fill down work. The offset angle for each set of points is listed in the same row, so if I have set something up to look down from a certain point and find the first row with the right offset angle, and then look up from a certain point and do the same thing, then I will have the range that I would need to average for a given offset. Then I would just need to put these in the average function using the right column. So if I can put that all in one long function then it should work, and if I set it up right then when I fill down it will just search for the next offset.
I still have to play around with it when I get off of work, but does that sound like it should work?
As for the PS3 UPCB file, I dont have a PS3. If you want I could test out the joystick on the computer games I have since you changed that.
I was thinking each vertical column would be a data point, with some specifier for the data point in row 1, the actual data in row 3, and your intended calulated mean, deviation, whatever, in subsequent rows underneath. What I’d do is make an extra row with a flag to show if that column contains data to process. Make a cell in the A column with the value ‘=IF(A$2="",0,1)’. Copy it to the clipboard, select and entire row, and paste it. Every cell on that row will have a 1 if there is data in that column, and a 0 if its blank. Make another cell that is just the sum of that flag row; now you have a cell that contain the number of data points you have. Getting the mean is just the sum of the data row divided by that number of data points. Computing your EE% and delta% should be easy.
Throw up some sample data like how youd like it and I’ll see what I can come up with.
D’oh! It will help with your doujin games, but you’ll probably want to wait for a regular release; the hex doesn’t have program button support in it.
Acutally I cant believe I didnt think of this before. There is a column in the data that has the offset angle so for getting the average I can use a SumIf and a CountIf function. And if I set up the value I want it to look for as a cell in a column that just has a list of all the values then when I fill down I will get all the averages.
The only problem is that I cant do the standard deviation this way. At least not unless I make a couple other columns in the data, but that would just add to the work that I am trying to bypass.
I think I have somthing that will work for both averages and standard deviations using the Match and CountIf fucntions but I have to see if what I am trying to do is possible first. Basically I would use the match and countif would be able to give me the first and last row of every set of points. But in order to make this useful I would have to put functions in front of a column header in the equation and I am not sure if excel allows that. If they do then I am set, but if not I will have to find something else for the deviation.
It sure would be nice if there was an AverageIf and StdevIf function.