Results 1 to 15 of 30
Thread: Formula Help (1997)

20031108, 03:48 #1
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula Help (1997)
I need some help on the following formula. It works perfectly as written. I want to replace the reference to

20031108, 10:18 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (1997)
You really didn't give much explanation of what you want to accomplish with this formula. It looks like you are trying to do some date calculations and have a large numger of "criteria" that you have chosen to use boolean logic rather than IF statements with ANDS
I don't see why replacing (P4=30) and/or (P4=59) with (S4 = "Y") or (S4<> "Y") should not work. (though I admit I don't really understand the formula)
You list (S4=Y), it should be (S4 = "Y") if "Y" is the letter Y, (S4=Y) will have S4 compared to a range named Y (and if you do not have one, you will get a result that is the #name error.
You might consider using [UPPER(S4)="Y"] incase people can enter "y" instead of "Y" in cell S4.
WIthout any further info, that is really the only advice I can give, other than, I would imagine that the formula could be simplified somewhat since you have a lot of repeating elements. It also might be clearer with if statements containing ANDs
Steve

20031108, 17:18 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula Help (1997)
Larry, you can attach the spreadsheet to a post so long as the file size is under 100Kb. Remove or disguise any confidential or proprietary information that you do not wish to publish (including anything in Properties), and it's probably best to cut the spreadsheet down to the minimum needed to demonstrate the issue.
John ... I float in liquid gardens
UTC 7ąDS

20031109, 01:37 #4
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (1997)
John,
Thank you for your reply. I am a Railroad conductor, and am trying to put together a spreadsheet to help compute pay. This formula addresses pay for being held at the awaf from home terminal.
I have used routing codes for held away to reflect numbers 101 to 300. My goal here is to add a reference column,

20031109, 02:45 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (1997)
Could you provide a spreadsheet with a few examples of your data and the results you expect (along with logic). I don't know about John (or anyone else) but I find the logic difficult to follow, and some examples might clear it up.
Steve

20031109, 03:30 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula Help (1997)
Like Steve (only more so) I'm at a loss to help you because of both the complexity of what you are asking, but more important, not knowing what kind of values are in all the cells referenced by the formula (which makes recreating the formula very speculative). You should be able to get the spreadsheet to postable size by:
1. turning all precedent formulas to values, that is, in your example, turn everything in cells A5,D5,E4,E5,F5,H4,I4,N4,Q5, etc. into values
2. leaving only a dozen or so rows of data, represeting most of the possible conditions
3. deleting all sheets but the sheet containing the formula in question
4. deleting all macro modules (if any)
.. but if that doesn't do it, get a copy of WinZip or other ZIp utiliity of your choice and zip the file and then post it.
Judging by the repetition of the same cells in your formula, I 'm guessing that a lookup table will simplify things greatly, but without knowinhg what is in those source values, it's very hard to get started.John ... I float in liquid gardens
UTC 7ąDS

20031109, 04:46 #7
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (1997)
Here is another explanation with an attached (partial) spreadsheet. I heve eliminated the lookup tables from other sheets, but for this column, I don't think they are necessary.
HELD AWAY PROBLEM
The math in the formula works perfectly for column

20031109, 16:47 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (1997)
It is very difficult to follow your logic. Could you state the possible conditons with IF statements (for us) with ANDs and ORs. They don't have to be excel formulas, just the logic and criteria. I still don't understand the concept. It doesn't help that the examples you showed all had at least one criteria that wasn't met so all the values in Col AT were 0. The only one that wasn't, was the one with the "S" criteria which you said does not calculate correctly.
In essence, your formula calcs:
Put a null if there is no date in the row
If previous Offduty "place" is "home" or if current onduty is the same as prev off duty then Held away = 0
But if both Offduty "place" is not "home" AND if current onduty is different than prev off duty, then check Routing.
If routing <=101
get 8 hours for each whole days
+ amount of time over 16 hours (if routing = 100, then the time based on Onduty time, else routing based on departure time)
If routing is over 101
get total elapsed time over 16 hours (if routing = 300, then the time based on departure time, else routing based on onduty time).
But I am still confused about which formula we are working on. The formulas in AT use different constants! Typically the goal is to have one formula and copy it down the column. If you are putting different formulas (with different constants) in each row, it might be easier to make a simpler formula that is less specific (though I could be missing something, since I don't understand).
From what I can grasp, you can improve the formula in AR by using:
<pre>=IF(ISBLANK(A3),"",MIN(I3E3+(E3>I3),1/3))</pre>
In AS:
<pre>=IF(ISBLANK(A3),"",MAX(I3E3+(E3>I3)8/24,0))</pre>
AT can be simplified by using:
=IF(ISBLANK(A4),"",(H3<>N3)*(D4=H3)*((Q4<=101)*((A 4A3(I3<E3))*8/24+MAX(E4*(Q4<101)+F4*(Q4=101)I316/24,0))+(Q4>101)*(A4+E4*(Q4<300)+F4*(Q4=300)A3I3(I3<E3)16/24)))
But this doesn't answer your initial question, about adding the "S" column, whihc I admit, I have no clue to the logic you want to achieve.
Please detail the LOGIC for the formula. Also some example data with results for a variety of conditions would be nice to compare formulas we create with the correct results to see if the formulas are working.
Steve

20031109, 18:43 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula Help (1997)
Larry, I'm going to take this a step at a time. Will the following do you what you want for column S? Enter it in S3, copy down.
=IF(AND(Q3>200,Q3<301),"Y","N")
If this is correct, do you have any problem with leaving it as
=AND(Q3>200,Q3<301)
so that it returns TRUE or FALSE? If so, in your column AT formula in row 4, you can replace instances of (S5="Y") with just S5, and copy down. Does that then correct the formula in column AT, or do you need more done?John ... I float in liquid gardens
UTC 7ąDS

20031109, 19:07 #10
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (1997)
No, this will not work. The original formul referred to column Q, and if the value was 101, it computed straight held for part of the formula, and continued held, with a max of 8 hours pay per 24 hour period till departing. If column Q had a value of 300, then we would stay on pay till going back on duty, no mater how long we were at the away terminal. My gaol here is to add a column (S) that when tagged with a "Y", both scenario's for held would come into play when deadheading (stay on pay till departing vs going on duty). The formula does not work when a "Y" is placed in column S. The computed times are way off. here is a copy of the formula with an attached copy of the spreadsheet. play with the times, and you will see what happens. Column S ONLY whendeadheading while on held pay.
=IF(ISBLANK(A4),"",PRODUCT(H3<>N3,D4=H3,Q4<=101,IN T(A4+E4*(Q4<101)+F4*(Q4=101)A3I3(I3<E3))*8/24+MAX((A4+E4*(Q4<101)+F4*(Q4=101)A3I3(I3<E3))INT(A4+E4*(Q4<101)+F4*(Q4=101)A3I3(I3<E3))16/24,0))+PRODUCT(H3<>N3,D4=H3,Q4>101,(A4+E4*(Q4<300) +F4*(Q4=300)A3I3(I3<E3))16/24))
The following has references placed for colums S. Formula works until I place a "Y" in colums S. When a Y is placed, the math is off.
=IF(ISBLANK(A4),"",PRODUCT(H3<>N3,D4=H3,Q4>100,INT (A4+E4*(Q4<101)+F4*(S4="Y")A3I3(I3<E3))*8/24+MAX((A4+E4*(Q4<101)+F4*(S4="Y")A3I3(I3<E3))INT(A4+E4*(Q4<101)+F4*(S4="Y")A3I3(I3<E3))16/24,0))+PRODUCT(H3<>N3,D4=H3,Q4>101,(A4+E4*(Q4<300) +F4*(S4="Y")A3I3(I3<E3))16/24))

20031109, 20:00 #11
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (1997)
Please see attached
It is very difficult to follow your logic. Could you state the possible conditons with IF statements (for us) with ANDs and ORs. They don't have to be excel formulas, just the logic and criteria. I still don't understand the concept. It doesn't help that the examples you showed all had at least one criteria that wasn't met so all the values in Col AT were 0. The only one that wasn't, was the one with the "S" criteria which you said does not calculate correctly.
=IF(ISBLANK(A4),"",PRODUCT(H3<>N3,D4=H3,Q4<=101,IN T(A4+E4*(Q4<101)+F4*(Q4=101)A3I3(I3<E3))*8/24+MAX((A4+E4*(Q4<101)+F4*(Q4=101)A3I3(I3<E3))INT(A4+E4*(Q4<101)+F4*(Q4=101)A3I3(I3<E3))16/24,0))+PRODUCT(H3<>N3,D4=H3,Q4>101,(A4+E4*(Q4<300) +F4*(Q4=300)A3I3(I3<E3))16/24))
In essence, your formula calcs:
True: Put a null if there is no date in the row
True, can only get paid if

20031109, 22:53 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (1997)
Is this what you are looking for?
=IF(ISBLANK(A4),"",(H3<>N3)*(D4=H3)*((Q4<=101)*((A 4A3(I3<E3))*8/24+MAX(E4*(Q4<101)+F4*(UPPER(S4)="Y")I316/24,0))+(Q4>101)*(A4+E4*(Q4<300)+F4*(UPPER(S4)="Y")A3I3(I3<E3)16/24)))
If it is not correct, could you give a small spreadsheet (I don't need all the "extra" columns, delete the contents and hide them if you want to keep the col references) with the pertinent columns filled in with data that gives the incorrect answers and then provide the correct answers (doesn't have to have a formula, but maybe some logic).
I can't check the accuracy of the results unless I know the correct answer. If you could provide a small table with results that "test" the calc limits I can check the formula easier.
Another comment: The fromulas could also be made simpler if you entered in date/times instead of just times. If you had date/times you can add and subtract them directly without some of the gyrations you do now.
If at all possible if you could create a table of the possible permutations of the various criteria columns (D/H,H/N,Q,S) and how you calculate with the date/time cols(A,E,F,I) [Col G the arrival time, does not seem to be important as it is not used] it might be easier to generalize a formula. D/H has 2 (eq or not equal), H/N has 2 (eq or not equal), Q has 3 (<100, 101200, 201  300) and S has 2 (="Y" or not equal to " Y") so there are only 24 permutations. It would help me to see the calcs for each one.
Steve

20031110, 05:26 #13
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (1997)
Steve,
Your formula did not work. Here is a reduced copy of my work with references working as written. Referenct to routing code 101 and Routing code 300 work fine, and extends the time till departing. Keep in mind that if routing codes of 101:200 are used, the max time alloted is 8 hrs per 24. Codes >200, can be an unlimited amount of time. When I replace the reference to use column S, and put a "Y" in it, the math does not work properly. See Attached.

20031110, 14:20 #14
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (1997)
Steve: I discovered a flaw in my spreadsheet when playing woith the times. When a routing code >100 & <201 are used, and the parameters for staying on pay till departing is entered, that the formula constantly calculates 8 hours. I changed the on duty time at the away terminal to refect 10:00, and departure time to show 10:30. This should give a time of 07:00 when not deadheading, and a time of 07:30 when deadheading. Again, showing 08:00 when deadheading. Formula is flawed. Can this be rewritten in If statements to make it simpler, albeit a longer formula. If so, please look at previous posting for explanation of held at away terminal.
Thanx
Larry

20031110, 19:48 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (1997)
Instead of your "megaformula" try this:
This should give your "current calculation".
In AU4 enter the intermediate formula:
<pre>=+A4+E4*(Q4<300)*(Q4<>101)+F4*((Q4=300)+(Q4=1 01))(A3+I3+(I3<E3))</pre>
This will give the total elapsed time from "OnDuty Start" to "OffDuty End". If Q4 =300 or Q4=101, the "OnDuty Start" will be the departure time (from Col F) as opposed to the "On Duty time" from Col E.
This can be copied down the column. It can be hidden if desired (currently your col AU is black, you could keep the black text in there esentially "hidden" from view. It is only an intermediate calc.
Then in AT4:
=IF(ISBLANK(A4),"",(H3<>N3)*(D4=H3)*((Q4<=101)*(IN T(AU4)*8/24+MAX(MOD(AU4,1)16/24,0))+(Q4>101)*(AU416/24)))
Will give you the two different ways to get out the numbers based on Q4<=101 (8 hrs / full day, plus the number of hours past 16 in a partial day), or Q4 >101, total elapsed time past 16 hours.
I still am not completely clear on the "S" column, but I "think" you want to use it as a signal to designate, using departure time versus work start time. If this is the case then you can use the formula in AU4:
<pre>=+A4+E4*(Q4<300)*(UPPER(S4)<>"Y")+F4*(UPPER(S 4)="Y")(A3+I3+(I3<E3))</pre>
Or even (if Q4 is always <300):
<pre>=+A4+E4*(UPPER(S4)<>"Y")+F4*(UPPER(S4)="Y")(A3+I3+(I3<E3))</pre>
If they do not yield the correct numbers, if you could tell me what values in the various cells do not work and what the correct value is, I can try to understand better.
If you want a "megaformula" you can replace the three AU4s references in the the AT formula, but (personally) this is a tiem when I think the intermediate formula will be better. The equation is easier to interpret and should calc faster, since you don't have to do the "AU4" calc 3 times for each formula, only once as an inermediate.
Hope this helps,
Steve