M
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!
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!