Table Lookup?

[agrajag42]

I'm trying to create a function that uses two variables to search a table and return a result.

I have a table with Current (in Amps) down the left side in the first column - from 1 to 25 Amps. The first row is Wire Gauges - 14, 12, 10, 8, 6, 4, 3, 2. The data in the table is the distance (in feet) which you can run that gauge wire with that current, with less than %2 losses.

I'm trying to create a function that supplies "Current" and "Distance" and comes out with the proper gauge to use. I thought maybe nested lookup functions would work, but even if they did it doesn't look like an elegant solution.

Anyone have any ideas?

Thanks!
/Ethan

[Palmetto]

See attached

[ChemistB]

He's looking for wire gauge with distance and amps being known.

[ChemistB]

Here's how I did it. I'm sure that it can be done without the intermediate step of pulling out that specific row but I'm drawing a blank.

1. Arraigning your table. To get this to work properly, the table needs to read decreasing distances from left to right.
2. I set up your inputs B1 = Amps, B2 = Distance, Table is from A5:K30
3. I pulled out the appropriate AMP Row in A32:K32 as follows
A32 = B1
B32 dragged right =

Please Login or Register  to view this content.

Now instead of a multirow table to extract from, you have a single row.
4. In B3, the calculation for wire gauge

Please Login or Register  to view this content.

If your outside an allowable range, you'll get "Outside Range"
See attachment and run a few inputs and see if that would work for you.

[WHER]

ChemistB's solution, plus an alternative without the helper row.

[ChemistB]

Very nice WHER.

[agrajag42]

Very, very impressive. This would have taken me a year. Thanks very much to all who helped! I learned a lot trying to figure all that out.

Two things:

1. My table (see below, just before quote) is in ascending order, not descending order. That breaks the MATCH function. I could change the table if I had to - but is there a way around that?

2. I've got one non-integer number in the "Amps" column, and a couple of non-integers in my "current" inputs. Since the OFFSET counts using the "current" (which is brilliant), this would probably break the formula.

Again, a thousand thanks to everyone. I had a lot of fun reading and figuring out the responses.

/Ethan


Amps 20 14 12 10 8 6 4 3
0.5 22.86 76.43 121.21 193.55 308.48 488.80 779.22 979.59
1 11.43 38.22 60.61 96.77 154.24 244.40 389.61 489.80
2 5.71 19.11 30.30 48.39 77.12 122.20 194.81 244.90
3 3.81 12.74 20.20 32.26 51.41 81.47 129.87 163.27
4 2.86 9.55 15.15 24.19 38.56 61.10 97.40 122.45
5 2.29 7.64 12.12 19.35 30.85 48.88 77.92 97.96
6 1.90 6.37 10.10 16.13 25.71 40.73 64.94 81.63
7 1.63 5.46 8.66 13.82 22.03 34.91 55.66 69.97
8 1.43 4.78 7.58 12.10 19.28 30.55 48.70 61.22
9 1.27 4.25 6.73 10.75 17.14 27.16 43.29 54.42
10 1.14 3.82 6.06 9.68 15.42 24.44 38.96 48.98
11 1.04 3.47 5.51 8.80 14.02 22.22 35.42 44.53
12 0.95 3.18 5.05 8.06 12.85 20.37 32.47 40.82
13 0.88 2.94 4.66 7.44 11.86 18.80 29.97 37.68
14 0.82 2.73 4.33 6.91 11.02 17.46 27.83 34.99
15 0.76 2.55 4.04 6.45 10.28 16.29 25.97 32.65
16 0.71 2.39 3.79 6.05 9.64 15.27 24.35 30.61
17 0.67 2.25 3.57 5.69 9.07 14.38 22.92 28.81
18 0.63 2.12 3.37 5.38 8.57 13.58 21.65 27.21
19 0.60 2.01 3.19 5.09 8.12 12.86 20.51 25.78
20 0.57 1.91 3.03 4.84 7.71 12.22 19.48 24.49
21 0.54 1.82 2.89 4.61 7.34 11.64 18.55 23.32
22 0.52 1.74 2.75 4.40 7.01 11.11 17.71 22.26
23 0.50 1.66 2.64 4.21 6.71 10.63 16.94 21.30
24 0.48 1.59 2.53 4.03 6.43 10.18 16.23 20.41
25 0.46 1.53 2.42 3.87 6.17 9.78 15.58 19.59

My

ChemistB's solution, plus an alternative without the helper row.

[agrajag42]

And is there an easy way to flip my table to be descending?

[ChemistB]

Yes, it does need to be switched. I copied and "insert copied cells, 1 column at a time.

I modified the equations best I could but in some cases, WHER and I disagree. I don't have time now to determine where the error is. Maybe WHER will stop by. Check to see if which is correct (e.g. at 2 amps, 30 feet, I get 12 guage and he gets 14).

[WHER]

Indeed i had to slightly tweak my formula because of the addition of the "0.5"
amps row at the top of the table.

[agrajag42]

Perfect.
Thank you both very much.