Cell Value Plus ( or Minus) Upper (or Lower) Limit

[Igorin]

Hello,

I ' trying to find a formula for the following situation.

I have 2 adjacent intervals (length, in meters). Each interval has an upper
and a lower limit. Each interval has it's own row and each of these rows has
it's corresponding upper and lower limits, each in it's own cell.

Every day work progress is uploaded into a table. This work progress gives
the start and end points of the daily work. One day work can take place only
in Interval 1 or 2; the following day it might start inside Interval 2 and
end in Interval 1; and the day after it it might start in Interval 2 and end
in Interval 1 (the opposite of the previous case).

I need a formula that takes the daily start and end points and calculate how
much distance was covered for each interval.

For example:

Interval 1: from 223 to 347 meters.
Interval 2: from 347 to 510 meters.

Daily Progress for today: from 329 to 419 meters.

I need a formula that does the following calculations:

Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329
(lower limit for daily progress) = 18 meters
Length of progress in Interval 2 = 419 (upper limit for daily progress) -
347 (lower limit for Interval 2) = 72 meters

And I need to take into consideration what I mentioned previously:

- One day work can take place only in Interval 1 or 2;
- The following day it might start inside Interval 2 and end in Interval 1;
- The day after it it might start in Interval 2 and end in Interval 1.


Thank you very much for all your help!

 

[vezerid]

A9:B9 contain the endpoints of interval 1
A10:B10 contain the endpoints of interval 2

A12:B12 contain the endpoints of work accomplished today.
In C12, calculate the portion of work that falls within interval 1:

=SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))>=$A$9)*(ROW(INDIRECT
(A12&":"&B12))<$B$9))

In D12, calculate the portion of work that falls within interval 2:

=SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))>=$A$10)*(ROW(INDIRECT
(A12&":"&B12))<$B$10))

If other endpoints are in A:B below row 12 you can copy the formula
down.

HTH
Kostis Vezerides

 

[Igorin]

Hello, Vezerid!

Thank you for taking the time to help. The formula you sugest returns a
formula error. Do you have an idea of what the cause might be?

 

[shg]

Answered in your other post.

 

[Igorin]

Sorry, when I copied it I left a space inbetween. That was the cause of the
error.

It works now! thank you very much for the help!!!

 

[Igorin]

Thank you very much for taking the time to help!

I'll take a look a it as soon as I have enough time to give it the attention
it deserves. Thanks again!

 

[Igorin]

Hello,

I'm not getting the correct results.

I just verified manually and the results are not correct.

I'm using the formula sugested by shg.

Thank you anyway for taking the time to help!