[gunn1]

I am quite a novice at Excel and all its possibilities.
With that being said, I have been able to produce a self calculating Time sheet/Pipe Progress report in a template form.
What I am having trouble with is figuring out how to make the Auto Sum function notice the form of measurement refered to as stationing.
Were the normal formual would Look like 0'+100' = 100' In the stationing format it would look like this 0+00-1+00= 100'. Also in Pipe or street work you may start at 0+00 and end at 1+25 (=125') The following day you would start at 1+25(where you left off the day before)and end at say station 2+50. Which would look like this 1+25-2+50=125'.

Is there a way to get Auto Sum to "notice" these numbers? If so which Auto Sum/Data link would I use?

Oh, and I am running Windows 7 with the newest edition of Microsft Office/Student

Any Information would be greatly appreciated, Thank you.

Gunn

[Advertisements]

Hello gunn1,

I have a couple questions that might help me or others reading your request. Hope you don't mind...

• How is your data entered now?
• What is your current data layout?
• Are there any relationships between the "stationing" numbers and standard measurements? i.e. you give an example: 0+00-1+00= 100'
• How many values would be entered per day?

I think that is all at the moment so thanks for any additional info you can provide.

Sincerely,

dm

[dm27]

Hello gunn1,

I have a couple questions that might help me or others reading your request. Hope you don't mind...

• How is your data entered now?
• What is your current data layout?
• Are there any relationships between the "stationing" numbers and standard measurements? i.e. you give an example: 0+00-1+00= 100'
• How many values would be entered per day?

I think that is all at the moment so thanks for any additional info you can provide.

Sincerely,

dm

[dsenette]

this seems to be completely proprietary numbering...and i don't think that's a scale that excel is going to be able to figure out. you'd probably need to make a custom input interface to allow the operator who's used to one type of input (stationing) and then have that info converted over to standard numbering.

i'm not familiar with stationing but it looks like you're doing incremental measurements. so on day one you start at 0 feet and work till you've laid 100 feet of pipe (0+00 - 1+00) then the next day you start at 100 feet and work till you've laid 100 feet of pipe (1+00 - 2+00)...in standard numbers you're doing aggregate calculations. with the stationing you're doing incremental calculations. you're not adding/subtracting the final number, you're adding/subtracting the difference between the starting number (which is variable) and the ending number

a simple (ish) vb app on top of the excel worksheet could do it.

have some simple fields that would allow for the operator to input the data as they know it...say a start time, end time, start station, end station then you'd do the calculations on that information that's entered. you may need to up the station fields to 4 total (two for start and 2 for end).


if the start was 00+100 and the end was 00+300 (so they laid 200 feet that day)
for the station fields you could have it like this:

lets assume the fields are: start1, start2, end1 and end2

start1 would be 00
start2 would be 100

end1 would be 00
end2 would be 300

then for the calculations you would end up with:
start1+start2=station1 (100)
end1+end2=station2 (300)
total=station2-station1 (200)

in the excel spreadsheet you could have 3 columns set up. for the station info. start, end, total

in the start/end columns you could have it actually display the input that the user entered in station format (00+100) then the total column would actually show the numerical output of the total calculation

[dsenette]

might have gotten too complex on that idea...but it would still work

alternately you could use the RIGHT() and LEFT() operations to extract the numbers from a column where the number is entered as 00+00

just made a simple spreadsheet:

StartStation EndStation Total
00+00 01+25 125
01+25 02+25 100
02+25 03+50 125

in the total column i have the formula:

=ABS((LEFT(A2,2)&RIGHT(A2,2))-(LEFT(B2,2)&RIGHT(B2,2)))

to translate some of that into english
ABS() gives you the absolute value of whatever's between the ()

(LEFT(A2,2)&RIGHT(A2,2) gives you the first 2 characters from cell A2 and the last 2 characters from cell A2 as one number (you could make this 3 or 4 or 700, but the number entered in column A would have to have numbers in those positions)

(LEFT(B2,2)&RIGHT(B2,2) gives you the first 2 characters from cell B2 and the last 2 characters from cell B2 as one number (you could make this 3 or 4 or 700, but the number entered in column A would have to have numbers in those positions)

you subtract those numbers and you've got your total feet

[gunn1]

Thanks dm27 and desentte for your understanding the issue of "stationing".
I did implement the code desentte sent and it works Excellent!!!Now the issue is the Template requires the results to be shown in these cells.
--------A------------C-----------------D-----------------E--------------F----------G-----------------------------
-3---Footage= ABS=----Plan STA start=---------To STA end=
-5---Footage= ABS=----Plan STA start=---------To STA end=
-7---Footage= ABS=----Plan STA start=---------To STA end=
-9---Footage= ABS=----Plan STA start=---------To STA end=
11--Footage= ABS=----Plan STA start=---------To STA end=
13--Footage= ABS=----Plan STA start=---------To STA end=
15--Footage= ABS=----Plan STA start=---------To STA end=
16Total Ftg= SUM

I have tried substituting the cells in your code with the cells needed for my template but it does not work, but it should-shouldn't it?? The sheet is unprotected, And is open to editing ranges, but this still does not help. All of the absolutes are in column "C" all starts are in "E" and all ends are in "G" with a grand total of absolutes (C3 through C15)in cell C16. I know your code works because I used it on a new/empty worksheet, I just cannot get it to work in the cells my template requires.
I hope this is not to much info or really confusing, may be Time for me to pickup a copy of Excel for Dummies!! Thank you,
Gunn

[dm27]

Hello gunn1,

Based on your template, and working with dsenette's formula (very slick by the way) would the next STA Start value always equal the prior STA End value? Such as my screen shot indicates?



If so, you would just need to set the appropriate Start cell to the equal the previous End cell.

E3=G2, E4=G3, continue as far as you need

The ABS formula in starting in cell C2 should work fine, once you adjust the cell references and then drag down (I only went down 3 lines in my example.

=ABS((LEFT(E2,2)&RIGHT(E2,2))-(LEFT(G2,2)&RIGHT(G2,2)))

Hope this helps,

dm

[gunn1]

Sometimes the Stationing will start where it was left off the day before, or we may start on a different pipe line
than the one previous.
You are right it is slick, and since my last post I have figured out the code for each respective cell. Works like a charm!!! Now my next hurdle is to get the auto sum to work, buy that I mean to add up the totals of the results of the Absolutes. I tried the "normal way" with the auto sum, it only works if there is a result in every absolute/total. If there is even one cell with #### in it that is what the Grand total cell shows(####). Is there any way that this problem can be rectified??

Thanks again for yuor time and consideration,
Gunn

[dm27]

Hi gunn1,

Have you tried to expand the size of that cell? Those # marks are usually place holders when Excel cannot display the entire cell value.

Are you getting other errors or name in the Total cell like Value? or Name? That would point to formula error somewhere.

[gunn1]

I did expand the cell and now I get the #VALUE! sign in all the absolute value cells. Although they do change to the correct values once the stationing values are entered.
So they do get the job done, and even when I used the original code on a blank worksheet It works great with the exception of the #VALUE! in the absolute cell Until the start and end data are entered. I have included a PDF showing  Partial_daily_report_PDF_3_13_2010.pdf   194.46KB   322 downloads you will see at the bottom of footage where it says total ftg.
(in red) is where im trying to get the absolutes to add without receiving an error messages.
Thank you very much you all have been a big help.
Gunn

PS disregard the two totals at the very bottom of the page (also in red) Those are for a diferent application and are working great.

Attached Files

•  Template_3_13_2010.pdf   293.69KB   692 downloads

[gunn1]

I beleive I have found a answer for my problem, I think. If it were possible to use one zero as place holder in each station input location/cell that some how allows the ABS cells to remain at zero and also allows the channel to stay open for the Total Ftg option.
Gunn

[dm27]

Hello gunn1,

Give this a try in your ABS cells instead.

=ABS(IF(E17="",0,(LEFT(E17,2)&RIGHT(E17,2)))-IF(G17="",0,(LEFT(G17,2)&RIGHT(G17,2))))

The IF statements lets Excel substitute a zero for an empty value in either the Start or End value cells. If there is a value, then Excel will use the full formula. No need to have any place holders in your Start or End cells.

Have a great evening!

dm


[EDIT]- Change cell references to match your worksheet...

Edited by dm27, 13 March 2010 - 08:00 PM.

[gunn1]

Thanks again I will try this new code tomorrow am, will let you know how it goes.

good evening,
Gunn

[gunn1]

Well dm 27 the "IFs" have it!! Works flawlessly!! Also allows the Auto Sum path to remain open for the total footages at the bottom of the ABS column. So one big THANK YOU for your help, it is greatly appreciated !!
Gunnar.

[dm27]

Howdy gunn1,

Glad GTG was able to help you out with your questions...

I find the IF formula very handy (I use it fairly often in my own projects).

Enjoy the rest of the weekend and come back soon!


Best regards,

dm

[dsenette]

glad DM got you straight with the if function it's extremely useful

another option would have been to not use the autosum function. it doesn't tolerate empty/null values. a simple addition function in the total cell would have ignored the empties