trying to get data out of a table based on multiple variables

[aslawson]

Hello all,

I am trying to write a program to extract data from a table based on a series of parameters any help would be greatly appreciated as it is prooving to be beyond my excel knowledge.

ie

from the first column i want to identify rows that add up to a value that I input into a cell ie "x" that also correspond to the lowest value of the sum of the same rows in fourth column and the highest value of the same rows in the third column.

I also want to ensure that the identified rows add up to a value in column two that is either in a range that I can set, equal to, more than or less than. "y"

ie if x = 100,000 and y > 45,000

70000	35000	105000	$ 2,065.00
71000	35000	106000	$ 2,094.50
72000	35000	107000	$ 2,124.00
73000	35000	108000	$ 2,153.50
74000	35000	109000	$ 2,183.00
75000	35000	110000	$ 2,212.50
76000	35000	111000	$ 2,242.00
77000	35000	112000	$ 2,271.50
50000	22500	72500	$ 1,475.00
78000	35000	113000	$ 2,301.00
79000	35000	114000	$ 2,330.50
51000	22500	73500	$ 1,504.50
80000	35000	115000	$ 2,360.00
52000	22500	74500	$ 1,534.00
53000	22500	75500	$ 1,563.50
54000	22500	76500	$ 1,593.00
55000	22500	77500	$ 1,622.50
56000	22500	78500	$ 1,652.00
25000	10000	35000	$ 737.50
57000	22500	79500	$ 1,681.50
58000	22500	80500	$ 1,711.00
26000	10000	36000	$ 767.00
59000	22500	81500	$ 1,740.50
60000	22500	82500	$ 1,770.00
27000	10000	37000	$ 796.50
61000	22500	83500	$ 1,799.50
62000	22500	84500	$ 1,829.00
63000	22500	85500	$ 1,858.50
28000	10000	38000	$ 826.00
64000	22500	86500	$ 1,888.00
65000	22500	87500	$ 1,917.50
29000	10000	39000	$ 855.50
66000	22500	88500	$ 1,947.00
67000	22500	89500	$ 1,976.50
30000	10000	40000	$ 885.00
15000	5000	20000	$ 442.50
68000	22500	90500	$ 2,006.00
69000	22500	91500	$ 2,035.50
31000	10000	41000	$ 914.50
32000	10000	42000	$ 944.00
16000	5000	21000	$ 472.00
33000	10000	43000	$ 973.50
34000	10000	44000	$ 1,003.00
17000	5000	22000	$ 501.50
35000	10000	45000	$ 1,032.50
36000	10000	46000	$ 1,062.00
18000	5000	23000	$ 531.00
37000	10000	47000	$ 1,091.50
38000	10000	48000	$ 1,121.00
19000	5000	24000	$ 560.50
39000	10000	49000	$ 1,150.50
40000	10000	50000	$ 1,180.00
20000	5000	25000	$ 590.00
41000	10000	51000	$ 1,209.50
42000	10000	52000	$ 1,239.00
21000	5000	26000	$ 619.50
43000	10000	53000	$ 1,268.50
44000	10000	54000	$ 1,298.00
22000	5000	27000	$ 649.00
45000	10000	55000	$ 1,327.50
46000	10000	56000	$ 1,357.00
23000	5000	28000	$ 678.50
47000	10000	57000	$ 1,386.50
48000	10000	58000	$ 1,416.00
24000	5000	29000	$ 708.00
49000	10000	59000	$ 1,445.50
5000	1000	6000	$ 147.50
6000	1000	7000	$ 177.00
7000	1000	8000	$ 206.50
8000	1000	9000	$ 236.00
9000	1000	10000	$ 265.50
10000	1000	11000	$ 295.00
11000	1000	12000	$ 324.50
12000	1000	13000	$ 354.00
13000	1000	14000	$ 383.50
14000	1000	15000	$ 413.00
4000	0	4000	$ 118.00
3000	0	3000	$ 88.50
2000	0	2000	$ 59.00
1000	0	1000	$ 29.50

<colgroup><col span="4"></colgroup><tbody>
</tbody>

<colgroup><col span="6"></colgroup><tbody>
</tbody>

Thanks

Andrew

 

[oldbrewer]

please restate the question with only 5 rows and 2 digit numbers

can you have any number of numbers in first column adding to the total
if so it is a lot of combinations

 

[aslawson]

Hello oldbrewer,

Please see a table below with 5 rows and max of 2 digit numbers.

As to the first column unfortunately it can have any number of combinations however typically there is only one solution that matches all the criteria and this is typically dependent upon the 4th column.


x=80
y < 20

10 2 12 3
20 3 23 6
30 4 34 9
40 5 45 12
50 10 60 15

 

[oldbrewer]

so 40,30,10 50,30 50,20,10 all sum to 80

so col 2 11 - 14 - 15

fourth column is 24, 24, 24

third column is 91, 94, 95

please restate your criteria in these terms

 

[aslawson]

So with this number set there were three possible options in the 1st column that summed to 80 as you identified.

Of these three possibilities I was then looking for the lowest value in column 4. (This scenario they are all the same)

At the same time look at column 3 for the highest value which happens to be 95.

therefore seeing that 95 is the highest identified then the option identifying rows 50 20 10 would be the solution to this problem

so 40,30,10 50,30 50,20,10 all sum to 80

so col 2 11 - 14 - 15

fourth column is 24, 24, 24

third column is 91, 94, 95

please restate your criteria in these terms

 

[oldbrewer]

what if column 4 was 24,24,23 and fourth column 91,94,95

does a single lowest value in column 4 take precedence over a single highest value in column ?