Can Excel Read a Chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For many years, when we were helping a customer select a model size, we had a
preprinted form that would guide us through the multi-step process of
gathering the hard data from the customer and plugging that data into the
various formulas. In addition, there are four different charts that provided
constants for the formulas. These constants were selected according to the
hard data provided by the customer. Someone finally put all our formulas into
an Excel program which has sped the process tremendoulsy, but the constants
must still be looked up and plugged in manually. Is there a way to put these
charts in the program? For instance, "If the motor speed is X and the tubing
size is Y, then read across the X row to the customer's X and down that
column to the customer's Y and use the number in that cell in the formula on
page 1, C35". All ideas, comments, and suggestions will be greatly
appreciated. Thanks, K.
 
Here's one way:

Motor speed = column A
Tubing size = row 1

...........A..........B..........C..........D
1....................0.5.......0.75......1.0
2......1000......10.........17........25
3......1500......12.........22........30
4......2000......18.........25........37

Lookup motor speed 1500
Lookup tubing size 0.75

F1 = 1500
E1 = 0.75

=VLOOKUP(F1,A1:D4,MATCH(E1,A1:D1,0),0)

Result = 22

Biff
 
Wow! That is outstanding.
Thanks, K.

T. Valko said:
Here's one way:

Motor speed = column A
Tubing size = row 1

...........A..........B..........C..........D
1....................0.5.......0.75......1.0
2......1000......10.........17........25
3......1500......12.........22........30
4......2000......18.........25........37

Lookup motor speed 1500
Lookup tubing size 0.75

F1 = 1500
E1 = 0.75

=VLOOKUP(F1,A1:D4,MATCH(E1,A1:D1,0),0)

Result = 22

Biff
Click to expand...
 
VLOOKUP is just too cool. Now that I know about VLOOKUP, I looked it up in
HELP. Even knowing how it works now, I don't think I could have accomplished
this from the instructions, but your instructions worked for me. Thanks.
Three of the four charts are up and running fine. The fourth is being
stubborn. It's layout is nearly identical to one of the others, which is
working fine. The only thing that raises a question is that both charts key
off of the same piece of raw data. However I have tried entering the data
twice - in two separate cells and the formula for the second chart continues
to respond with #N/A. All my links are to good data and the formulas look
identical except for the differences of the data locations and such. I've
rebuilt the formulas several times. Some by by point and click, some by
typing in the cell, and some by typing in the fx line. I've even cut and
pasted the working formula into the other cell and simply made the changes
for the data locations and such and it still returns #N/A. I'm quite new to
Excel and my competancy leans toward Computerly Challenged, so I'm quite sure
I'm the problem. Would the fact that the two charts key off of the same raw
data have anything to do with this problem? Thanks, K.
 
Hard to say what the problem might be.

Can you describe the table and what it is you're looking up? Also post the
formula.

Biff
 
Thank You T. Valko.

The chart and formula that works:
Table 2, Weight of Fluid, (Wf)
Pump BoreWt of Fluid
inches lbs/ft
1 1/16 0.3838
1 1/4 0.5313
1 1/2 0.7650
1 3/4 1.0413
1 25/32 1.0788
2 1.3600
2 1/8 1.5353
2 1/4 1.7213
2 1/2 2.1250
2 3/4 2.5713
3 1/4 3.5913
3 3/4 4.7813
4 3/4 7.6713
Wt of Fluid
=VLOOKUP(Sheet1!E5,Sheet2!D2:E16,MATCH(Sheet2!D17,Sheet2!D2:E2,4),4)

"Sheet1!E5" is where the size of the pump bore is entered on Sheet 1. For
instance, a pump bore of 1 3/4" would have a column of fluid that weighs
1.0413 lbs per foot.
"Sheet2!D17" is the first cell below the chart, and that cell contains the
words "Weight of Fluid".
The formula, =VLOOKUP...4), is in the cell right below "Sheet2!D17". The
result of that formula - such as the 1.0413 - is then inserted into a formula
on Sheet 1. All these formulas work as hoped.

The chart and formula that doesn't work:
Table 3, Constant, (K)
Pump BoreConstant
inches "K"
1 1/16 0.132
1 1/4 0.182
1 1/2 0.262
1 5/8 0.308
1 3/4 0.357
1 25/32 0.370
2 0.466
2 1/8 0.526
2 1/4 0.590
2 1/2 0.729
2 3/4 0.882
3 1/4 1.231
3 3/4 1.639
4 3/4 2.630
Constant
=VLOOKUP(Sheet1!E5,Sheet2!G2:H17,MATCH(Sheet2!G18,Sheet2!G2:H2,3),3)

"Sheet1!E5" is where the size of the pump bore is entered on Sheet 1 - the
same data used in the previous chart. For instance, if the pump bore is 1
3/4", then you multiply by the Constant 0.357.
"Sheet2!G18" is right below the chart and contains the word "Constant".
The formula, VLOOKUP...3), is in the cell right below the "Sheet2!G18". That
result - such as the 0.357 - is then inserted into a formula on Sheet 1.
While this formula is identical to the previous formula except for the
locations of data, this formula returns #N/A.

While typing this I hit on the idea that it may not be necessary for formula
data expected to be unchanging to come from previous cells so I changed
Sheet2!D17 in the first formula from the cell reference to "Wt of Fluid" and
it works fine. The same kind of change in the second formula still returns
#N/A.

Any ideas you may have to offer will be greatly appreciated.
Thanks, K.
 
Ok....

If your tables are only 2 columns there's no need to use the MATCH function
to find the column index number. So, your first formula (that you say works)
should be written like:

=VLOOKUP(Sheet1!E5,Sheet2!D2:E16,2,0)

The MATCH function is only needed if you don't know which column is the
result column. Since there are only 2 columns in the table the 2nd column is
obviously the result column.

So, your 2nd formula should also be written like the one above:

=VLOOKUP(Sheet1!E5,Sheet2!G2:H17,2,0)

Biff
 
Oh Thank You so much. That works great. This has streamlined the sizing
process greatly. Customer service is unbelieveably better. K.
 
You're welcome. Thanks for the feedback!

Biff

K said:
Oh Thank You so much. That works great. This has streamlined the sizing
process greatly. Customer service is unbelieveably better. K.
Click to expand...
 
Back
Top