This complex problem is a continuation of this thread
we started with....
TMShucks provided this, which addresses the problem of automatically determining which cells to add together =SUMIF($F$1:$F$2376,$F1,$B$1:$B$2376)I have a program that outputs a CSV file with 8 separate strings of numbers (each string is roughly 300 numbers together, and each sequence is listed sequentially). These strings need added together.
In the attached sheet the values i need to combine are in the B column. The J column has the formula i use to add the values together =SUM(B1,B298,B595,B892,B1189,B1486,B1783,B2080), and by cascading this formula down the J column i can add the first 300 values.
The unfortunate aspect is that the first cell of each string isn't always in the same position. After B1 the beginning cell address can change with different outputs. For example, another output might require =SUM(B1,B315,B605, etc)
I have figured a means of filtering the data by dividing other properties of the data output. (the data output is columns A - D). If the value in column F equals 1 then I need the corresponding address of the B column in the same row inserted automatically into the formula.
Example.xlsx
Moving into the more advanced problem.....
TMShucks makes an important observation about the data strings, which is actually the very source of the problem, the variable string lengths;The string of values has this same repeating sequence of 8 outputs that need added, but the outputs continue and continue one after the other.
After each series of 8 are added, another series of 8 begins, but the ending point of each string can be different because of the fluctuating string lengths.
I have been altering these values manually for months with each output, the most tedious undertaking in the history of man I believe.
Here is another version of the same chart, except expanded. The other columns (N, R) have the other successive strings. This usually stretches out to 16 series in length, but there is an upload limitation for the file size so i truncated it.
The rule still holds that if you filter column F by the 1 value the corresponding B columns are those that need added, it is just a continuation of the same theme ad nauseum.
These columns can be listed sequentially if needed, just each equaling 300 values before the next 300 begin. I hope this is described correctly!
Complexity!!.xlsx
The length inconsistency is fine, I still require each string added up 300 times, even if the strings are different lengths.(falls within an accepted sampling error rate) So i would need the first set of 8 combined for 300 cells, followed by the next string of 8 combined for another 300, repetitively for 16 iterations. In teh Complexity!! example above, i have each successive string combined into a new column, but if they were listed sequentially in one column that would also be fine, if it simplifies things.Just as a feed into the next thread, when you create it, I have been trying to understand the relationship between the cells. See the attached updated example. However, I am finding some inconsistency in that the first eight cells are separated by 297, the second set of cells are separated by 296 and the third are separated by 296 or 295.
Bookmarks