On Jun 22, 9:06*pm, GS wrote:
shriil presented the following explanation :





On Jun 21, 12:44 am, GS wrote:
shriil used his keyboard to write :

On Jun 20, 1:06 pm, "Charabeuh" wrote:
Just another link:http://www.mediafire.com/file/yz5namhy2yj/Overlap.xls
"shriil" a crit dans le message de groupe de
discussion :
...
The website to the link you posted is infected.- Hide quoted text -
- Show quoted text -

Thanks. Downloaded it. But how to calculate the Cumulative Overlap ? I
need to calculate the same when two or more equiopment are out. The
moment no. of out equipment becomes 1, the overlap hours stop counting
and then may restart if another equipment goes out.

The formula I provided for the Overlap column does this, and the
TotalOverlap cell only calcs when 2 or more items are logged out. I
thought that's what you wanted!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Yes Garry.. you are right.. actually I am also trying to depict it in
a columnar form for display (courtesy Charabeu) as well as using your
formula for getting the final Cumulative overlap hours

Yeah, that might be doable. Charabeuh's solution is awesome but it will
run out of columns long before you use up the rows. That's why I
suggested sticking with your original layout. I'm sure a pivot tble
could be constructed to do what you want based on the original layout.

Note that I only accounted for the basic columns needed to implement
the solution. You could add more fields as required.

Also, if you think about my solution in the context of a
'point-of-sale' utility it might make more sense. Basically, all you
need to do is enter the item in columnA and the macro sets the rest up
for logging out, and the other macro concludes the logging in. So other
fields like name of user, pricing (if appropriate), and so on could be
added to make it a more useful tool.

Charabeuh's amazing solution strikes me as being more of an analysis
tool, which you could probably use as well. Maybe include a pivot table
if so desired, that works on my solution. Also, this sorting you want
to do on Charabeuh's table might be easier to code if you assigned
indexes to the columns/rows that you can use in the formulas instead of
the Column() function. That way, you could sort by entire row and the
columnar data will adjust automatically to the new position of the
first column data.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Very true Garry.. as you rightly pointed out, Charabeuh's table is
more like an analysis tool.

Basically I am trying to amalgamate both yours and Charabeuh's for
the user to understand how he arrives at the Cumulative Overlap Hours
by the columnar representation of Charabeuh but derived initially
through your macro. Because for the user, the macro would be a black
box and he would be not in a position to understand whether the
calculation is allright or going awry. Here, Charabeuh's side-by-side
columnar depiction shall help him to understand and check. But yes ...
I am run out of columns. ..