Spreadsheet calculations with offsets

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

Hello,
I am currently new to working with MS excel sr-1 and need some help on
create a spread sheet that can calculate various offset distances based on a
length of a line and the beginning and end distance.
Can someone explain to me how this is done.
Thanks
 
Can you explain what you want a bit more please?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy Mann said:
Can you explain what you want a bit more please?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
Hello,
Thanks for your reply. See sketch
Beginning Station of the line 0+00 ________________________
500+00 (Line is 500 feet) end
Distance between the two lines at 0+00 = 12' and at 500+00 = 24'
New line Station of the line
-------------------------------- Bottom line in a angle

Example of calculations:
Station offset
0+00 12'
250+00 18'
500+00 24'

How I came up with the calculations is the following:
What I would like is the beginning and ending distance of the two lines and
the length are given and have the spreadsheet calculate the offset
(distance) for any length of the line. So based on the example above if I
wanted a to calculate the distance in the middle I would subtract
500+00-0+00/2 = 250' and 24+12/2=18'
 
The calculation, as I think you are aware, is a simple proportion; but, in
order to help you, it would be useful to know if your stations are "cell
formatted" to look like you showed (in other words, do you have the cells in
your Station column custom formatted with 0+00)? Same question about the
"foot mark" on the values in your Offset column? It would also be useful to
know what cells (row and column) your Beginning and Ending stations and
their Offsets are in (that is, how is your data laid out?

Rick
 
Rick Rothstein (MVP - VB) said:
The calculation, as I think you are aware, is a simple proportion; but, in
order to help you, it would be useful to know if your stations are "cell
formatted" to look like you showed (in other words, do you have the cells in
your Station column custom formatted with 0+00)? Same question about the
"foot mark" on the values in your Offset column? It would also be useful to
know what cells (row and column) your Beginning and Ending stations and
their Offsets are in (that is, how is your data laid out?

Rick
Click to expand...
Hello,
Yes you are correct in the simple proportion and if it is possible I would
like it in a cell format maybe something like this.
A B
1 Station offset distance
2 0+00 (I input) 12 (I input the no.)
3 any distance(I input) calculated value
4 any distance(I input) calculated value
5 500+00(I input) 24 (I input the no.)

I hope I explain myself clear on this it does not have to be to fancy
 
For future reference, you should follow the answer style used by the person
responding to you. I top posted my response, so you should top post your
reply to me. Why? So someone reading this thread in the Google archives can
follow the order of the responses.

Okay, I thought you might have been suggesting that order in your first
message. There is a problem with doing it that way. Only one of your "fixed"
points is really fixed (the 0+00 station), the other is at the end of the
data. This makes it hard to give you a formula because any formula will be
dependent on address of the "fixed" points and one of your "fixed" points
will vary with the amount of intermediate points between them. Is there any
chance you can lay your data out in a way that your Start and End Stations
and their Offsets are in fixed locations (maybe in the first 2 rows) with
the intermediate points following them? That way your formulas won't be
overwritten by data as the number of intermediate points change. If you
would rather not do that (that is, you want to stick with the layout you
showed), are you allowed to run a macro which could perform the
calculations?

And you didn't answer my question about how the plus sign is place in your
Station numbers or how the "foot mark" is placed at the end of the Offset
distance. Are they put there by a custom format of the cells or are you
physically typing them in?

Rick
 
Sure no problem for now lets try to make it easy and then we can try the
macro
for variable stations length nothing to fancy.

Station offset
row 1 0+00 (I input) 12 (I input a no.)
row 2 5+00 (I input) 24 (I input the no)
row 3 any distance(I input) calculated value
row 4 any distance(I input) calculated value

Ok, the plus sign station will be inputted by me and if I understand
correctly about
the footmarks after the offset and stations (I input a no.) you really don't
need it that
was just used for explanation purposes.
Thanks again for your help I hope we can get it to work
 
Hi Mark,

I'm not clear on the 0+00 input, but this may be
what you are looking for.

In your example,
Put this in B3 and drag to B4
=TREND(B$1:B$2,A$1:A$2,A3)

That's assuming your sample data is in A1:B2

HTH
Martin
 
I was going to give you this formula for B3...

=B$1+(B$2-B$1)*(A3-A$1)/(A$2-A$1)

and tell you to copy it down, but I think MartinW's TREND formula is the
better way to go. But neither will work if you are inputting your stations
with the plus sign as those entries will be text, not numbers. We could
modify the formulas to account for your manually entered plus sign, but the
formulas will be much longer and less efficient (because of some extra
function calls that will be required. An alternative is to enter the numbers
without the plus sign and let Excel put it in for you automatically; the
plus sign won't really be in the number itself (so the number will remain a
number), but rather it will simply be in the display of the number. Try this
and see what you think. Select Column A (click the Column A header), right
click the selection and select Format Cells from the popup menu, select
Custom from Category list and type 0+00 in the Type field, click the OK
button. Now, for any numbers you now have in Column A, retype them without
the plus sign (so for 250+00, you would type 25000)... Excel will put the
plus sign in for you (if you look at the Formula Bar for any of these
values, you will see there is no plus sign in the number, so the number is
still a number for calculation purposes). You can get the foot mark into
your entries in Column B in the same way... just follow the same steps for
Column A as above except use 0' in the Type field instead.

If this is not how you want to handle your entries, let me know and I'll
give you the longer formulas to use.

Rick
 
Hello,
Ok, the + is the way engineers refer stations of a line so for example 0+00
= 000.00 and 5+00= 500.00.
 
I know that... I was a Civil Engineer (Road Design for the NJ Department Of
Transportation) for 32+ years. The formatting method I gave you will still
show you the plus sign in your stations (and the foot mark on your offsets
if you choose to do that also) and leave the station as a number so it can
be used in calculations (not only for calculating the offset, but any other
calculations you might want). The only thing you have to remember is to not
manually type in the plus sign. In case you still do not see the future
benefit of this, then here is my formula, modified to let you continue to
manually type in the plus sign...

=B$1+(B$2-B$1)*(SUBSTITUTE(A3,"+","")-SUBSTITUTE(A$1,"+",""))/(SUBSTITUTE(A$2,"+","")-SUBSTITUTE(A$1,"+",""))

This formula assumes the numbers in Column B are real numbers (no "foot
mark" on the end of them).

Rick
 
Hello,
Thanks its working perfectly but one minor thing right now it calculates one
station
at a time can you make it to calculate numerous stations along the line. If
it is to difficult
then forget it.
For example:
Station offset
row 1 0+00 (I input) 12 (I input a no.)
row 2 5+00 (I input) 24 (I input the no)
row 3 2+50 (I input) calculated value
row 4 3+50 (I input) calculated value
row 5 1+00 (I input) calculated value
 
After you place the formula in B3, click on the small black square in the
lower right corner of the cell and drag down to the row with the last
station on it... the formula will automatically be copied into the cells you
click/dragged down over. If you want to be able to click past the last
station (to be ready for other inputted intermediary stations), put this
formula in B3 and copy it down (as described above) instead... it is the
same formula as before, but with an extra IF function call to suppress any
error messages when there is no station show in Column A of that row the
copied/down formula is in...

=IF(A3="","",B$1+(B$2-B$1)*(SUBSTITUTE(A3,"+","")-SUBSTITUTE(A$1,"+",""))/(SUBSTITUTE(A$2,"+","")-SUBSTITUTE(A$1,"+","")))

Rick
 
Rick Rothstein \(MVP - VB\) said:
For future reference, you should follow the answer style used by the person
responding to you. I top posted my response, so you should top post your
reply to me. Why? So someone reading this thread in the Google archives can
follow the order of the responses.
Click to expand...
....

As has been pointed out many times in the past, if YOUR response style
is predicated on Google's sorted/indented thread listing, then there's
NO REASON WHATSOEVER to quote ANYTHING from previous responses. It
only demonstrates laziness and don't-give-a-damn attitude.

OTOH, if you're trying to write responses with some thought for people
who use newsreaders, then posting responses to individual quoted
sections BELOW those sections and SNIPPING everything to which you're
NOT responding ALWAYS makes more sense.

So, reply w/o quoting if you want, otherwise bottom post. Anything
else shows a lack of thought/thoughtfulness.

If Outlook Express bottom posted by default, we wouldn't be having
this argument.
 
Back
Top