Test if cell reference is inside the range

[DrLove73]

I have range of cells I want to compare for overlap (Thread:http://www.mrexcel.com/forum/excel-...ap-other-2-item-arrays-those-two-columns.html), and I managed to devise the formula that SUMs (for testing) using parts of range before and after the cell, buf I am having trouble with cases where cell is first of last in array/range.

Example:

Working range is $D$2:$D$10, Windows, Excel 2010.

[TABLE="class: grid, width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]C
[/TD]
[TD="width: 64, align: center"]D
[/TD]
[TD="width: 64, align: center"]E
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1
[/TD]
[TD="width: 64, align: center"]0
[/TD]
[TD="width: 64, align: center"]0
[/TD]
[TD="width: 64, align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]14,50
[/TD]
[TD="align: center"]18,21
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]14,50
[/TD]
[TD="align: center"]18,21
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]11,70
[/TD]
[TD="align: center"]15,41
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]12,50
[/TD]
[TD="align: center"]16,20
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]12,50
[/TD]
[TD="align: center"]16,20
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]10,41
[/TD]
[TD="align: center"]14,12
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]6,75[/TD]
[TD="align: center"]10,45
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]4,79
[/TD]
[TD="align: center"]8,50
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3,70
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Formula for E3-E9 is as follows, this one is for E4 ($D4 refference)

=SUM($D$2:OFFSET($D4;-1;0);OFFSET($D4;1;0):$D$10)

I first protected from reference not pointing to correct cell (A0 that can not exist) (For E4):

=IF(ISREF($D$2:OFFSET($D4;-1;0));SUM($D$2:OFFSET($D4;-1;0);OFFSET($D4;1;0):$D$10);SUM(OFFSET($D4;1;0):$D$10))

but then realized that table/range will most likely be somwhere in the middle of worksheet.

So I need to compare if OFFSET($D2;-1;0) and OFFSET($D10;1;0) is out of bounds for range ($D$2:$D$10).
I can work with separate tests if OFFSET($D2;-1;0) is above first cell D2, and OFFSET($D10;1;0) is bellow D10.

 

[DrLove73]

Ok, I have solved it, I hope. For cell E4 ("$D4" reference) and range D2:D10 it's:

=IF(ROW(OFFSET($D4;0;0))=ROW($D$2);SUM(OFFSET($D4;1;0):$D$10);IF(ROW(OFFSET($D4;0;0))=ROW($D$10);SUM($D$2:OFFSET($D4;-1;0));SUM($D$2:OFFSET($D4;-1;0);OFFSET($D4;1;0):$D$10)))

or

=IF(ROW(OFFSET(<current cell of interest>;0;0))=ROW($D$2);SUM(OFFSET(<current cell of interest>;1;0):$D$10);IF(ROW(OFFSET(<current cell of interest>;0;0))=ROW($D$10);SUM($D$2:OFFSET(<current cell of interest>;-1;0));SUM($D$2:OFFSET(<current cell of interest>;-1;0);OFFSET(<current cell of interest>;1;0):$D$10)))

 

[DrLove73]

Last formula was suposed to show this:

=IF(ROW(OFFSET("cell to exclude";0;0))=ROW($D$2);SUM(OFFSET("cell  to exclude";1;0):$D$10);IF(ROW(OFFSET("cell to  exclude";0;0))=ROW($D$10);SUM($D$2:OFFSET("cell to  exclude";-1;0));SUM($D$2:OFFSET("cell to exclude";-1;0);0

,but I made HTML mistake.