HLOOKUP?

[MNProp]

I have a large range of data in tabular form. It is a table of minimum
verticle curve lengths for stopping sight distance for highway design based
upon design speed and the algebraic difference (A) between the approach grade
and departure grade of a curve. The design speed is from 25 to 60 with a
step of 5 (B2:I2) while my A values are from 0.8 to 10 with a step of 0.1
(A3:A95).

What I want to be able to do is input the certain design parameters and
reference the table to determine if the design is compliant with its
corresponding length and therefore design speed on the table.

I have three cells for my arguments (design parameters): verticle curve
length, approach grade, and departure grade. I figured I could use the
HLOOKUP function to find the compliant length and corresponding design speed.
Here is how I did it: I easily figured out the algebraic difference, then I
used that number and used the match function to find it in the A column where
my "A" values happen to be. Then, I added 2 to the match function because I
needed to reference the row relating to the array in the HLOOKUP function. I
checked several numbers along the entire data set and each time, it
determined the correct row corresponding to the array. For instance, 0.8,
which on the entire sheet is on row 3 is actually on row 1 of the array.
This was true to the function.

So now I have:

lookup_value: design verticle curve length (input argument)
table_array: B3:I95 (minimum curve length constants)
row_index_num: what my match function yields with the A value
range_lookup: either true* or 0*
*I tried either one because I want the function to go to the next largest
value that is smaller than the input argument in that row. I can't have a
smaller curve length then the minimum allowable for a certain design speed.
For instance, say my A value is 1.1, with a verticle curve length of 122',
and a design speed of 45 mph. On the table, The minimum curve lengths of 40
mph and 45 mph corresponding to an A value of 1.1 are 120' and 135'
respectively. The table tells me that I need a curve length of at least 135'
but my design length is 122'. This means I need to redesign because it is
not compliant. Its a simple enough calculation, but multiply that by how
many crest and sag verticle curves (each with their own tables) that could be
on a job and you have some work to do.

My problem is, there are places in the table where it works perfectly but
there are more places where it will go to a larger value then my input
argument, all in the same row. I have been trying anything I can, but it
boggles my mind why it works from some places but not on others. Every row,
by nature of the algorithm that determined the table, is in ascending order.
So, the function nows how to get to the correct row, but it yields incorrect
values from the row. I have noticed that it seems to work correctly on the
portions of the table where the columns are the same number (minimum lengths
available for design at that speed) from about row 3 to row 26 across each
column but starts to break down after. For instance, column one is 25 mph
and the minimum length of curve for that speed is 75' so that goes for 33
rows until the equation in the algorithm exceeds it. With my limited
knowledge, I was able to notice this, but it is beyond my scope to comprehend
why this might be the cause of my problem. If anyone has any comments or
advice, I would very much appreciate it!

 

[Bernie Deitrick]

Mr. Highway Engineer,

Make up a small table (three or four columns and three or four rows) that shows your problem, and
post the formula that you use, with the reuslt that you get, and the result that you want. Your
description is just too hard to follow.

HTH,
Bernie
MS Excel MVP

 

[MNProp]

This is part of the section that works:
V (mph)
A/ 25 30 35 40 45 50 55
_____________________________________________________________________
0.8/ 75 90 105 120 135 150 165
0.9/ 75 90 105 120 135 150 165
1.0/ 75 90 105 120 135 150 165
1.1/ 75 90 105 120 135 150 165
1.2/ 75 90 105 120 135 150 165

This is part of the section that doesn't:

4.0/ 75 125 182 244 305 379 457
4.1/ 81 132 190 253 316 390 470
4.2/ 86 139 197 261 325 401 483
4.3/ 91 145 204 269 334 412 495
4.4/ 96 150 211 277 343 422 506

The values contained in the body of the table are minimum curve lengths for
the designated speed given the A value.

My inputs are:
Length of Verticle Curve (ft)
Approach Grade (g1)
Departure Grade (g2)

So A=abs(g2-g1) then round up to the next higher tenth. 3.12 becomes 3.2
and so on.

So what I want it to do is find the row that the A value is on, look for my
input design verticle curve length on that row. If it finds it exactly, I
want to output that value. If it doesn't find it exactly, I want it to spit
out the next largest value on that row that is smaller than the input curve
length. Eventually, I want to be able to output the speed that corresponds
to the output minimum VC length available as well as the minimum VC length.

The function as I have it right now breaks down at A values of about 2.8 to
3.0 and higher (up to A=10.0, table stops after this).

Thank you very much!

 

[Bernie Deitrick]

Mr. HE...

Can you post the formulas that you use to extract the data? Assume that each table starts in cell
A1 (with headers in row 1? and delta grade in column A?) , and that your other data (the stuff you
are using in the lookup) are in cell of row 1 starting in column, say, M.

My inputs are:

So use cells:
M1 = Length of Verticle Curve (ft)
N1 = Approach Grade (g1)
O1 = Departure Grade (g2)

and then post the formula...

HTH,
Bernie
MS Excel MVP

 

[MNProp]

Bernie,

Algebraic Difference "A":
=ROUNDUP(ABS(N1-O1),1)

Match (determine row for HLOOKUP ref):
=MATCH(N4,A5:A97)
*If I am not mistaken, the row that the HLOOKUP function refers to is from
its own array and not the entire sheet. If this assumption is true, this
function does indeed do that. If I were to have an A value of 0.8, the
function kicks out 1 even though on the sheet it is on row 5. 0.8 is the
first row of my table to extract from.

HLOOKUP:
=HLOOKUP(M1,B5:I97,N5,TRUE)

Notes:
-A5:A97 is the section where the "A" values are contained.
-B5:I97 is the body of the table to extract info from.
-N5 is where my MATCH function is housed.
-From B4 to I4 is where the column titles are. They are the speed for each
column. Starts with 25 mph and goes to 60 mph.
-All I am doing is inputting three values to the sheet and not moving or
copying anything so I don't see the need to lock any of the arguments
contained in the functions.

Thanks

Mr. "Trying to be a" HE

 

[Bernie Deitrick]

Mr. "Trying to be a" HE,

You seem to be using speed in cell M1, not Vertical Curve, and using the HLOOKUP formula to return
Vertical Curve (Note that your spelling is incorrect - engineers (I'm one) will notice that....).
Is that correct?

Anyway, I think your problem is that you are ignoring the header row (row 4). Try using:

=MATCH(N4,A4:A97)

instead of

=MATCH(N4,A5:A97)

and use

=HLOOKUP(M1,B4:I97,N5,TRUE)

instead of

=HLOOKUP(M1,B5:I97,N5,TRUE)


HTH,
Bernie
MS Excel MVP

 

[MNProp]

Mr. Deitrick,

Now I am embarrassed. I usually pride myself on my spelling! Oh well; as
you can tell, I am still green in the field! Anyway, my inputs have to be
for design verticle curve length, approach grade, and departure grade. I
know that design speed is a factor in the design of the verticle curve, but
that is really the basis of this exercise - making sure that the curves
comply with the speeds. I want to be able to use this sheet in the future
for minor reconstruction projects where the verticle profile of the existing
road is not altered in a major way. The body of the table to extract from is
curve lengths based upon an A value and a speed, all in English units.

I tried your solution and it returned the same values. I can't make the
connection on how row 4 on the sheet factors into the function, but that
could be because I can't portray the entire sheet on here for you to look at.

I guess what is really bothering me, and is the basis of my problem, is that
even though I have told HLOOKUP to return either my exact value or the next
largest value that is less than my design VC length by telling it TRUE, it
yields a larger value. I am tempted to conclude either this problem goes
deeper than is possible to portray on this board, or I am not versed enough
to supply you with the pertinent information; probably a combination of the
two with an emphasis on the latter.

If you wish to continue helping me, I'll do my best to give you the
information, but if not, I understand. Thank you anyway for taking the time
to help me up to this point.

Mr. "Trying to be a" HE "who spells poorly"

 

[Bernie Deitrick]

Mr. HE,

Okay, I think I understand now.

With this formula in N5

=MATCH(ROUNDUP(ABS(N1-O1),1),A:A,FALSE)

Use this formula to return the speed allowed on the curve (vertical curve
length in cell M1, speed values in B4:I4):

=INDEX(A4:I4,IF(ISERROR(MATCH(M1,OFFSET(A1:I1,N4-1,0),FALSE)),MATCH(M1,OFFSET(A1:I1,N4-1,0))+1,MATCH(M1,OFFSET(A1:I1,N4-1,0),FALSE)))

HTH,
Bernie
MS Excel MVP