Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Summing up a formula 2

Status
Not open for further replies.

MJSWTA

MIS
Apr 3, 2007
4
US
I am pretty much a newbie to XL....
With that said here's the detail of what I'm trying to do:
We have driver routes which I use Maximum and Minimum to extract the beginning and ending times for the route (times are in seconds after midnight). I figure out the total hours for each route, subtracting out any breaks the driver took during the day. Here's my formula which does work correctly:
((Maximum ({Events.ActualDepartTime}, {EventStrings.EvStrName}) -
Minimum({Events.ActualDepartTime},{EventStrings.EvStrName})) / 3600) - Sum ({@breaks}, {EventStrings.EvStrName})

I've placed this in the EventStrings.EvStrName group header. Ok, so that all works but now I need to total up the hours for each route to get the total hours for a day. Apparently one can't sum a sum and it looks like using variable is the way to go. I've tried setting this up but having trouble. May I get some assistance? Thanks much. MJS
 
Assuming you have an outer group on date on change of day, create these formulas:

//{@reset} to be placed in the date group header:
whileprintingrecords;
numbervar sumrt;
if not inrepeatedgroupheader then
sumrt := 0;

//{@accum} to be placed in the route group header:
whileprintingrecords;
numbervar sumrt := sumrt + ((Maximum ({Events.ActualDepartTime}, {EventStrings.EvStrName}) -
Minimum({Events.ActualDepartTime},{EventStrings.EvStrName})) / 3600) - Sum ({@breaks}, {EventStrings.EvStrName})

//{@display} to be placed in the date group footer:
whileprintingrecords;
numbervar sumrt;

-LB
 
Thanks very much. This worked perfectly....now I have to figure out why.
 
Well, the variable is set to 0 in the reset formula, and the {@accum} formula is adding the value of the variable to itself each time it executes--which is based on the formula's location, in this case, in the group section. So it will accumulate the value of the formula in the group section. The final display formula shows the current value of the variable. You can't use the {@accum} formula because it would accumulate one more time if placed in the group footer, so you simply reference the variable and it shows the value as of the last accumulation.

The name of the variable (sumrt) is irrelevant--you could call it "x" or "snoopy" and it would still accumulate the same way. I usually go for a simple letter, but sometimes it makes sense to label the variables to reflect the calculation, if you have multiple variables.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top