J
J
I have a table of cost per pound based on weight and miles.
Column A is the miles (0 - 100, 101 - 250, 251 -500,..)
Weight across (0-300, 301 -450,...)
I need to find the cost per pound in the table based on these two
variables.
I've tried:
=INDEX($E$5:$H$7,MATCH(A18,D47,0),MATCH(B18,F4:H4,0))
but that only gives me the exact matches. I'm not sure how to find the
match within the ranges.
The whole thing would look like this:
miles pu weight
0 300 301 400 401 500
0 100 50 .1 .45 .55
101 200 100 .2 .35 .65
201 250 150 .3 .95 .75
So if I had 350lbs and went 120 miles, I need to get .35 and pu of
100.
Also, there will be three or four of these tables and each employee
has a table assigned to them. Suppose that we need to do a batch thing
at the end of the month. Ideally Crystal Reports could export to excel
and stick the weights, miles, and employee name on a spreadsheet. Then
excel displays the values. Right now, we have someone looking up all
of these figures then looking in the table, then filling out the
spreadseet. Any ideas?
Thanks
Column A is the miles (0 - 100, 101 - 250, 251 -500,..)
Weight across (0-300, 301 -450,...)
I need to find the cost per pound in the table based on these two
variables.
I've tried:
=INDEX($E$5:$H$7,MATCH(A18,D47,0),MATCH(B18,F4:H4,0))
but that only gives me the exact matches. I'm not sure how to find the
match within the ranges.
The whole thing would look like this:
miles pu weight
0 300 301 400 401 500
0 100 50 .1 .45 .55
101 200 100 .2 .35 .65
201 250 150 .3 .95 .75
So if I had 350lbs and went 120 miles, I need to get .35 and pu of
100.
Also, there will be three or four of these tables and each employee
has a table assigned to them. Suppose that we need to do a batch thing
at the end of the month. Ideally Crystal Reports could export to excel
and stick the weights, miles, and employee name on a spreadsheet. Then
excel displays the values. Right now, we have someone looking up all
of these figures then looking in the table, then filling out the
spreadseet. Any ideas?
Thanks