filter results, then average, then graph results

[j4str]

Im trying to sort through tons and tons of data for stock trading. I'm testing different indicators on different settings and then exporting the results to excel. A test could have as several hundred to several thousand different settings tested and then try it on anywhere from 6 to 30 stocks. I need a way to filter all of this data so that that it is usable. What i would like to do, is to toss out the outlying data and then average the other results and then create some kind of graph from the remaining data. I need to not only be able narrow down the area of the tests that had the best settings, but i also need to be able to compare that area to all the other indicators.


the original spreadsheet might look something like this:


Indicator #243

stock1 stock2 stock3 stock4 stock5 stock6
test1
test2 $-400 $16 $-12 $680 -$22 $3 = throw away -400 and 680, remaining average = $3.75
test3
test4
test5
test6
test7
test8
test9
test10

[Olly]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[j4str]

I ended up spending quite a bit of time on google and with the help of a friend getting what i wanted in google sheets. I paste all my results in to separate tabs for each stock that i am testing on. Then I get a tab that averages all of the results with the high and low discarded and one more tab that displays the top results.

1. We created a tab called "combined" that put all of the results in to one column.

2. We created a tab called "consolidate" that finds the sum, Max, and Min of all of the original columns from the stocks.

3. We created a tab called "average Results" that displays all of the original data as averages with the high and low discarded

4. We created a tab called "top results" that organizes them by the highest profit minus the high and the low






This all working well in google sheets, except that sheets has a maximum of 40,000 rows and and the consolidate page puts all 8 results in to one columns. 40k divided by 8 means i can only do about 5,000 passes on one spreadsheet and i am having to split the tests up in to multiple spreadsheets. I am regularly running 10,000 pass tests. The only reason i'm limiting it to 10,000 is that it would be difficult to deal with that on google sheets. If i could run a million passes on one spreadsheet, i would be. Can anyone help me recreate this spreadsheet in excel, but in a way that i can run as many passes as possible?