Hello,

I would greatly appreciate if you could help me witht the following problem.

I have two adjacent length intervals:

Interval 1 goes from 237 Km to 356 Km.
Interval 2 goes from 356 Km to 491 Km.

I also have a table with the daily start and end points of the work done for
that day:

Work on day 1: from 313 Km to 377 Km.
Work on day 2: from 289 Km to 357 Km.
(etc.)

Is there a formula that can calculate what is the daily length that falls
within each interval?

For example:

On day 1:
Work done on interval 1 = 43 Km (356 - 313);
Work done on interval 2 = 21 Km (377 - 356).

On day 2:
Work done on interval 1 = 67 Km (356 - 289);
Work done on interval 2 = 1 Km (357 - 356).

Thank you very much for the help!

--
igor

Maybe like this:

Code:
--------------------

--A-- -B- ----C---- ----D----
1 Begin End Interval1 Interval2
2 237 356
3 356 491
4 313 377 43 21
5 289 357 67 1
--------------------

In C4 and copied across and down

=MAX(0, MIN(C$3, $B4) - MAX(C$2, $A4) )


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30483

Well, this will work for the given data, but if I remember right, you had
other possible scenarios in a previous post, and without knowing other
scenarios, can't develop formula to handle those other scenarios.
In my sample, I have interval table set up as follows:
J4:K4 = 237 | 356
J5:K5 = 356 | 491

I have your two sets of stop/starts in the following cells
J8:K8 = 313 | 377
J9:K9 = 289 | 357

Formula in L8:
=VLOOKUP(J8,$J$4:$K$5,2)-J8+K8-VLOOKUP(K8,$J$4:$K$5,1)
And this was copied down to L9. This gave the results of 64 & 68 respectively.


--
** John C **


"Igorin" wrote:

Hello,

I would greatly appreciate if you could help me witht the following problem.

I have two adjacent length intervals:

Interval 1 goes from 237 Km to 356 Km.
Interval 2 goes from 356 Km to 491 Km.

I also have a table with the daily start and end points of the work done for
that day:

Work on day 1: from 313 Km to 377 Km.
Work on day 2: from 289 Km to 357 Km.
(etc.)

Is there a formula that can calculate what is the daily length that falls
within each interval?

For example:

On day 1:
Work done on interval 1 = 43 Km (356 - 313);
Work done on interval 2 = 21 Km (377 - 356).

On day 2:
Work done on interval 1 = 67 Km (356 - 289);
Work done on interval 2 = 1 Km (357 - 356).

Thank you very much for the help!

--
igor

Thanks a lot! It works!
--
igor


"shg" wrote:


Maybe like this:

Code:
--------------------

--A-- -B- ----C---- ----D----
1 Begin End Interval1 Interval2
2 237 356
3 356 491
4 313 377 43 21
5 289 357 67 1
--------------------

In C4 and copied across and down

=MAX(0, MIN(C$3, $B4) - MAX(C$2, $A4) )


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30483

Hello, John,

Thank you very much for taking the time to help.

The formula works fine to calculate the total amount, but I need to know
what amount falls inside of each interval.

I think that i will use the formula given by shg.

Thanks anyway for your time!

--
igor


"John C" wrote:

Well, this will work for the given data, but if I remember right, you had
other possible scenarios in a previous post, and without knowing other
scenarios, can't develop formula to handle those other scenarios.
In my sample, I have interval table set up as follows:
J4:K4 = 237 | 356
J5:K5 = 356 | 491

I have your two sets of stop/starts in the following cells
J8:K8 = 313 | 377
J9:K9 = 289 | 357

Formula in L8:
=VLOOKUP(J8,$J$4:$K$5,2)-J8+K8-VLOOKUP(K8,$J$4:$K$5,1)
And this was copied down to L9. This gave the results of 64 & 68 respectively.


--
** John C **


"Igorin" wrote:

Hello,

I would greatly appreciate if you could help me witht the following problem.

I have two adjacent length intervals:

Interval 1 goes from 237 Km to 356 Km.
Interval 2 goes from 356 Km to 491 Km.

I also have a table with the daily start and end points of the work done for
that day:

Work on day 1: from 313 Km to 377 Km.
Work on day 2: from 289 Km to 357 Km.
(etc.)

Is there a formula that can calculate what is the daily length that falls
within each interval?

For example:

On day 1:
Work done on interval 1 = 43 Km (356 - 313);
Work done on interval 2 = 21 Km (377 - 356).

On day 2:
Work done on interval 1 = 67 Km (356 - 289);
Work done on interval 2 = 1 Km (357 - 356).

Thank you very much for the help!

--
igor