Support Board
Date/Time: Sat, 23 Nov 2024 08:36:12 +0000
Post From: Challenging Spreadsheet operation
| [2024-09-02 18:17:44] |
| owendonk - Posts: 54 |
|
Hello, I'm trying to take a set of values and extract subsets that meet certain criteria. Specifically, I want to input the values in the range K3:AU3, remove repeat values and values below 15000, and identify all subsets with a range < 30. If multiple subsets include the same number, only include the subset with the most numbers and the smallest range. Then I want to output the highest and lowest values of each subset to formula columns so I can autodraw a rectangle for each range. Here is an example in python that mostly works. I may need to write it in ACSIL and make a custom study, but I don't know C++. Any help is greatly appreciated. The output of this python is [19164.75, 19145.55] [19593.975, 19600.969401041668, 19611.313802083336, 19590.625] [18367.946, 18369.6645] [19690.438802083336, 19695.675]. Ideally this would all be done in the spreadsheet with spreadsheet logic. data = [17346, 18735.525, 19164.75, 19593.975, 20983.5, 14136.5, 16752.054, 17560, 18367.946, 20983.5, 17346, 18369.6645, 18685.875, 19002.0855, 20025.75, 19690.438802083336, 19759.219401041668, 19532.188802083336, 19442.719401041668, 19848.688802083336, 19373.938802083336, 19600.969401041668, 19611.313802083336, 19590.625, 19621.658203125, 63.27272920927062, 59.38859038604226, 19145.55, 19255.575, 19475.625, 19695.675, 19805.7] # Remove values smaller than 10000 data = [x for x in data if x >= 10000] # Remove duplicate values unique_data = [] for x in data: if x not in unique_data: unique_data.append(x) data = unique_data # Identify subsets with range smaller than 30 subsets = [] used_numbers = set() for i in range(len(data)): if data in used_numbers: continue subset = [data] for j in range(i + 1, len(data)): if data[j] not in used_numbers and max(subset + [data[j]]) - min(subset + [data[j]]) <= 30: subset.append(data[j]) if len(subset) > 1: subsets.append(subset) used_numbers.update(subset) # Filter subsets to include only the one with the most numbers and smallest range if multiple subsets have the same numbers unique_subsets = {} for subset in subsets: subset_key = tuple(sorted(subset)) if subset_key not in unique_subsets or (len(subset) > len(unique_subsets[subset_key]) or (len(subset) == len(unique_subsets[subset_key]) and (max(subset) - min(subset) < max(unique_subsets[subset_key]) - min(unique_subsets[subset_key])))): unique_subsets[subset_key] = subset # Output all unique subsets that meet the criteria for subset in unique_subsets.values(): print(subset) Date Time Of Last Edit: 2024-09-02 18:19:06
|