Complicated Excel Calculation

[mbpaul1987]

Hi all,

I am wondering if I can get a little bit of help/ suggestions on how to solve my problem.

I am trying to create a form that automatically gives me a set of ranges based on a series of calculated data.

In my table I have a section that I use to enter the data (#, PQR, Pass, Size, Amps, Volts, Arc speed, Heat Input) this data gets entered from many potential sources. Next to that I have a calculated section where it calculates the values from Amps, Volts, Arc Speed, Heat Input to turn them into a range of +/- 20%.

What I need is for each calculated range I need the highest and lowest value based on Pass type, and Size. Here is where is gets tricky, each value from each range must fall within the other or the entire row must be eliminated from the entire calculation. An example of this is:

1	125	158
2	156	325
3	185	356
4	195	365
5	366	485

<tbody>
</tbody>

In this example 125 is the lowest value and it's max is 158, the next lowest value is 156 and its max is 325. Because 156 falls below 158 we can use that value and 325 becomes our new max. and you can carry through until row 5. after row 4 our low is still 125 but our max is 365. Because row 5's low falls outside the 365 max, the entire row must be eliminated from the calculation and all future calculations. This is repeated for all ranges in Amps, Volts, Arc speed, and Heat input and if one calculation falls outside the range for one the row must be eliminated from the calculations for the rest. The other thing that makes this even more complex is that the passes are often numbered 1a,1b, 2a, 2b, and I often have multiple PQR numbers. What needs to happen is if 3b from PQR 2 is eliminated then 3a from PQR 2 also need to be eliminated.

Finally what I would like it to do is either highlight the high and low range for each pass and size within each pass or better yet have it entered into a table at the bottom that gives me the ranges based on pass and size.

I will try to copy and past a version of what I have including the results I have in the bottom table. (I had to enter those values manually because I couldn't figure out the formulas needed to calculate it the way I want.)

I don't know if what I'm asking is even possible. but I thought I'd throw it out there to see if anyone has some ideas or possible fixes.

Thanks

			Enter Data	Amps Range	Volts Range	Speed Range	Heat input Range
#	PQR	Pass Type	Pass	Size	Amps	Volts	Arc Speed	Heat input	Low	High	Low2	High3	Low4	High5	Low6	High7
1	206	Root	1a	4	160	29	383	0.73	128.0	192.0	23.2	34.8	306.4	459.6	0.58	0.88
2	206	Root	1b	4	165	30	299	0.99	132.0	198.0	24.0	36.0	239.2	358.8	0.79	1.19
3	206	Hot Pass	2a	5	200	28	396	0.85	160.0	240.0	22.4	33.6	316.8	475.2	0.68	1.02
4	206	Hot Pass	2b	5	200	31	386	0.96	160.0	240.0	24.8	37.2	308.8	463.2	0.77	1.15
5	206	Fill	3a	5	175	28	250	1.18	140.0	210.0	22.4	33.6	200.0	300.0	0.94	1.42
6	206	Fill	3b	5	215	32	249	1.66	172.0	258.0	25.6	38.4	199.2	298.8	1.33	1.99
7	206	Fill	4a	5	185	28	251	1.24	148.0	222.0	22.4	33.6	200.8	301.2	0.99	1.49
8	206	Fill	4b	5	170	31.5	207	1.55	136.0	204.0	25.2	37.8	165.6	248.4	1.24	1.86
9	206	Fill	Stripa	5	185	28	762	0.41	148.0	222.0	22.4	33.6	609.6	914.4	0.33	0.49
10	206	Cap	5a	5	130	28	159	1.38	104.0	156.0	22.4	33.6	127.2	190.8	1.10	1.66
11	206	Cap	5b	5	135	29	118	1.99	108.0	162.0	23.2	34.8	94.4	141.6	1.59	2.39
12	2081	Root	1a	3.2	110	26	396	0.43	88.0	132.0	20.8	31.2	316.8	475.2	0.34	0.52
13	2081	Root	1b	3.2	135	29.5	238	0.73	108.0	162.0	23.6	35.4	190.4	285.6	0.58	0.88
14	2081	Hot Pass	2a	4	175	26	504	0.54	140.0	210.0	20.8	31.2	403.2	604.8	0.43	0.65
15	2081	Hot Pass	2b	4	170	26	445	0.6	136.0	204.0	20.8	31.2	356.0	534.0	0.48	0.72
16	2081	Fill	3a	5	230	29	563	0.71	184.0	276.0	23.2	34.8	450.4	675.6	0.57	0.85
17	2081	Fill	3b	5	224	32	522	0.82	179.2	268.8	25.6	38.4	417.6	626.4	0.66	0.98
18	2081	Fill	4a	5	185	28	338	0.92	148.0	222.0	22.4	33.6	270.4	405.6	0.74	1.10
19	2081	Fill	4b	5	200	29	321	1.08	160.0	240.0	23.2	34.8	256.8	385.2	0.86	1.30
20	2081	Cap	5a	5	150	28	204	1.23	120	180	22.4	33.6	163.2	244.8	0.98	1.48
21	2081	Cap	5b	5	148	27.5	180	1.36	118.4	177.6	22	33	144	216	1.09	1.63
22	2082	Root	1a	3.2	130	30	552	0.42	104	156	24	36	441.6	662.4	0.34	0.50
23	2082	Root	1b	3.2	140	30	495	0.51	112	168	24	36	396	594	0.41	0.61
24	2082	Hot Pass	2a	4	250	32	547	0.88	200	300	25.6	38.4	437.6	656.4	0.70	1.06
25	2082	Hot Pass	2b	4	249	32	575	0.83	199.2	298.8	25.6	38.4	460	690	0.66	1.00
26	2082	Fill	3a	5	230	30	487	0.85	184	276	24	36	389.6	584.4	0.68	1.02
27	2082	Fill	3b	5	229	32	504	0.87	183.2	274.8	25.6	38.4	403.2	604.8	0.70	1.04
28	2082	Fill	4a	5	190	27	394	0.78	152	228	21.6	32.4	315.2	472.8	0.62	0.94
29	2082	Fill	4b	5	190	30	376	0.97	152	228	24	36	300.8	451.2	0.78	1.16
30	2082	Cap	5a	5	160	27	239	1.08	128	192	21.6	32.4	191.2	286.8	0.86	1.30
31	2082	Cap	5b	5	147	30	203	1.3	117.6	176.4	24	36	162.4	243.6	1.04	1.56

<tbody>
</tbody>

Table I need at the end

Pass	Size	Amps Allowed	Volts Allowed	Speed Allowed	HI Allowed
Root	3.2	88	168	21	36	191	662	0.34	0.9
Root	4	128	192	23	36	239	459	0.58	1.19
HP	4	136	300	21	38	356	690	0.43	1.05
HP	5	160	240	22.4	37	309	475	0.68	1.15
Fill	5	136	276	22	38	165	675	0.57	1.99
Cap	5	104	192	22	36	95	287	0.86	2.39

<tbody>
</tbody>