Help for producing a calculator

O

oldruts

Can you please help me? I am trying to figure out how to do a looku
with this data.

Track Width 50 75 100 125 150
drop upto 60" 60.01 73.64 98.2 122.72 147.3
drop upto 80" 73.35 87.52 116.7 145.87 175.05
drop upto 100" 83.6 101.37 135.2 168.99 202.8
drop upto 120" 94.85 112.27 149.7 187.12 224.55


Enter Details
*Width*
*Drop*

Price


If you were to add a value into the bold boxes (width and drop) I wan
a formula which looks up
the relevant price.
for Example if you were to type 65 in the width box and 72 in drop
would like C16 to come up with the price in C3.
I'm struggling to work out the function I need.

Many Thank
 
R

robert111

add a new left column to your table A, B and C, ie any drop up to 60 is
coded A, 60.01 to 80 is coded B and anything above 80 is coded C

Now create droptable as below

1......A
60....A
60.01...B
80.....B
80.01...C
999...C

when you input the drop ie 65

=vlookup(65,droptable,2)

this returns B

do the same for widths, using P,Q,R,S T for the codes (adding new row
at the top)

Now you can use 2 match functions within an offset function

..........P.....Q......R.......S.......T
A
B..............87.52............
C


ASSUMING THE "P" IS IN CELL A2

=OFFSET($A$2,MATCH(C1,ABCNAME,0),MATCH(A3,PQRSTNAME,0))

where abcname is the range B1:D1 and pqrstname is range A2:E2


this returns 87.52
 
M

mr_teacher

Would this work for you?

If you have your data in a1 to f5 and change your text of "drop u
to60" etc" to just a number e.g. 60

Then if you can enter the Width in C13 and the drop in c14

You can then have the formula
=IF(OR(C13="",C14=""),"",HLOOKUP(C13,B1:F5,IF(C14<(A2+1),2,IF(C14<(A3+1),3,IF(C14<(A4+1),4,5)))))
to find the price?

Does that work for you?

Regards

Carl
 
Top