formula drag down problem

[ACCA369]

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Samples	Value_1	Value_2		Data_set2	Netural_set2
2	19	-90	90		5	25
3	10	-180	180		6	26
4	15	360	360		8	28
5	5	90	90		9	29
6	14	90	90		11
7	2	-90	90		12
8	34	180	180		14
9	14	90	90		15
10	11	90	90		17
11	29	0	90		18
12	0	-90	90		20
13	15	180	180		21
14	13	-90	90		31
15	4	-180	180		32
16	27	-360	360		34
17	25	0	720		35
18	25	0	720
19	27	-720	720
20	11	1440	1440
21	34	90	90
22	6	90	90
23	30	-90	90						formula column
24	18	180	180					1)	Value 1	iF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>&X27"),C2*-1))
25	1	-90	90					2)	Value 2	iFS(A13="","",B12=0,C12,B12<0,C12*2,B12>0,90)
26	8	180	180					3)	column E & F : data range used in formula
27	2	-90	90
28	24	-180	180					Pls note:
29	18	360	360					I have drag down formula in B & C column as much I require upto 70 rows
30	22	-90	90					input column is A, autofill column B & C based on formula
31	27	-180	180
32	25	0	360					my question is to find
33	29	0	360
34							1**	total number of data after last positive value
35									answer is 4
36
37
38							2**	find zero data after last positive value
39									answer is 2
40
41							3**	sum of all the negative values after last positive value
42									answer is -270
43
44							4***	Last positive number
45									answer is 360
46
47
48
Sheet5

Cell Formulas
Range	Formula
B2:B48	B2	=IF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>&X27"),C2*-1))
C3:C48	C3	=IFS(A3="","",B2=0,C2,B2<0,C2*2,B2>0,90)

 

[jasonb75]

Is there an error in this formula?

=IF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>&X27"),C2*-1))

Should it be

=IF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>"&X$27),C2*-1))

If so, what value is in X27 (and the subsequent rows if it should be relative).

 

[ACCA369]

Is there an error in this formula?

=IF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>&X27"),C2*-1))

Should it be

=IF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>"&X$27),C2*-1))

If so, what value is in X27 (and the subsequent rows if it should be relative).

Thanks for patiently reading the problem
YES THE correction in first formula B2 = as below
=IF(A2="","",IFS(COUNTIF($E$2:$E$37,A2),C2,COUNTIF($F$2:$F$37,A2),0,COUNTIF($E$2:$F$37,"<>"&A2),C2*-1))
its relative reference

The real issues are:
when the formula in B and C column are dragged to 48 rows ,
and i input the data in the A column from 1st row to 33th row
i want to find

1) number of data after last positive number ( answer : 4)

2) number of "Zero" value after the last positive number (answer : 2)

3) Sum all the negative values after last positive number ( answer : -270)

4) last positive number (answer: 360)