formula to multiple by -1 based on two unique conditions

[y3tter]

I have a sheet that lists geological formations with their vertical depth(in column C), in multiple locations. The locations are separated into groups and are identified that way in column A(Group 1, Group 2, Group 3, etc). Towards the middle of each group has the target geological formation of interest. I am wanting a formula in column D that subtracts the vertical depths and define how thick each formation is, but if it is listed as target or below, in each group, to multiply by negative one and if it is the last formation in the group to leave the cell blank.

I attached a small table for visual reference, column D is where I am wanting to have the formula.

https://www.dropbox.com/s/bx6kjdgltzciham/formation.xlsx?dl=0

 

[Scott Huish]

So for D2, would it multiply by -1 because 24 is less than 8930? or what?

Excel 2010
	A	B	C	D
1	Groups	Formation	TVD	thickness
2	Test One	CSTR	8797	24
3	Test One	MRMC	8821	36.5
4	Test One	MRMC E	8857.5	45.5
5	Test One	MRMC D	8903	14.5
6	Test One	MRMC B	8917.5	12.5
7	Test One	Target	8930	62
8	Test One	Target Base	8992	5.5
9	Test One	MRMC LS	8997.5	28.5
10	Test One	WDFD	9026	6.5
11	Test One	WDFD Chert	9032.5	17.5
12	Test One	WDFD Chert Base	9050	47.5
13	Test One	WDFD MID	9097.5	-297.5
14	Test Two	CSTR	8800	18
15	Test Two	MRMC	8818	42
16	Test Two	MRMC E	8860	42
17	Test Two	MRMC D	8902	14
18	Test Two	MRMC B	8916	15
19	Test Two	Target	8931	63
20	Test Two	Target Base	8994	4
21	Test Two	MRMC LS	8998	28
22	Test Two	WDFD	9026	4
23	Test Two	WDFD Chert	9030	18
24	Test Two	WDFD Chert Base	9048	49
25	Test Two	WDFD MID	9097
Sheet1

 

[mrshl9898]

I needed 2 columns for this.

E2 =IF(COUNTIF(A:A,A2)=COUNTIF($A$1:A2,A2),"",IF(OR(B2="Target",E1="AB"),"AB","BL"))
F2 =IF(E2="BL",C3-C2,IF(E2="AB",-(C3-C2),""))

 

[y3tter]

No, 24 is the value the formula should come up with. The formula would have to look for "Target" in column B and all formations including it and below would be multiplied by negative one, until it reaches the next group, "Test Two" in column A. D7:D12 would be multiplied by negative 1 and D13 would be blank, since it is the last formation in the Group "Test One"(Column A). D19:D24 would also be multiplied by negative one and D25 would be blank

 

[y3tter]

I needed 2 columns for this.

E2 =IF(COUNTIF(A:A,A2)=COUNTIF($A$1:A2,A2),"",IF(OR(B2="Target",E1="AB"),"AB","BL"))
F2 =IF(E2="BL",C3-C2,IF(E2="AB",-(C3-C2),""))

THANK YOU mrshl9898, that works perfectly!!!!