Im working on some data on a lab report finding the gear ratio of a 1200cc yamaha powered legends car knowing the driveshaft rpm, left wheel rpm, and right wheel rpm. Problem is for some reason some of the readings off the driveshaft RPM readings came off in the thousands instead of hundreds. example: Time(s) , Left Wheel , Right Wheel , Driveshaft, 1.97000E+00 ,7.50000E+01, 8.31445E+01, 2.84953E+03 1.98000E+00 ,7.50000E+01 ,8.31445E+01 ,3.42422E+02 1.99000E+00 ,7.50000E+01 ,8.31445E+01 ,3.42422E+02 2.00000E+00 ,7.50000E+01 ,8.31445E+01 ,3.24855E+03 2.01000E+00 ,7.50000E+01 ,8.31445E+01 ,3.24855E+03 2.02000E+00 ,7.50000E+01 ,8.31445E+01 ,3.24855E+03 2.03000E+00 ,7.50000E+01 ,8.31445E+01 ,3.24855E+03 I cant just "change" the data and make it right. I have to say we took the outliers off the last column and disregard that row all together if it does not meet a certain parameter. In this case it needs to say when averaging the gear ratio((Driveshaft rpm/lw +Driveshaft rpm/RW)/2) in a separate column to disregard anything over 4.0 another possibility is if the standard deviation is over "XX" to disregard it. How would one go about writing a formula for that? pics of said car
=if(abs(D1-average(D: D))>stddev(D: D)), " ", D1) " " is a blank (space) char. Populate column E with the function above (use fill down), and use the answers from E, if (isnumber(E1), insert your formula here, " ")
I dont exactly follow. I inputed 5 data rows and put that function in E and it gave me an error. So i changed it to this and still an error =if((D1-average(D1: D5))>stddev(D1: D5)), " ", D1) Thats saying if D1 minus the average of the 5 data points is greater than the standard deviation of those data points then it puts a blank in the data. if not it keeps d1 edit i got "=IF(D1>500," ",D1)" to work
Sorry, change the > to < and that's the behavior you asked for. Use the value if it's less than a standard deviation away from the mean, right? A lot will depend on how many you have that are off by an order of magnitude. You could simplify it quite a bit if you tested to see if it were the same order of mag... (The error you got was probably in the "Whole column" D: D notation. If I take the blank out between, the BBS puts a smiley in: D)
country boy... put your formula in from above... ((Driveshaft rpm/lw +Driveshaft rpm/RW)/2)... should look kinda like: =(D6/B6+D6/C6)/2 that gives you your ratio in that column... do that for every row then at the bottom of that column insert a "sumif" divided by a "countif"... kinda like this: =SUMIF(E6:E12,"<4")/COUNTIF(E6:E12,"<4") simplest way I could think of.... Problem is, in your sample data, none of the rows qualify as less then 4.... ????
okay the data has changed a bit. In some instances the left wheel or the right wheel has no RPM reading. So what was say alright if Left wheel rpm=0 then " ", same for right wheel. so there will be a column now for right wheel gear ratio, left wheel gear ratio, and the average of those two. problem is for the average when I input this forumla knowing that some of the E4 and F4 are blank and I do not want any gear ratios over 4 I get an error saying "#value" because of the blank lines. =IF(OR(E4=" ",F4=" ",(E4+F4)/2>4)," ",(E4+F4)/2) I want any of these three E4=" ",F4=" ",(E4+F4)/2>4 to make the average value blank.
