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.

	C	D	E
1	0	0
2	14,50	18,21
3	14,50	18,21
4	11,70	15,41
5	12,50	16,20
6	12,50	16,20
7	10,41	14,12
8	6,75	10,45
9	4,79	8,50
10	0	3,70
11	0	0

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

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.