formula drag down problem

ACCA369

New Member
Book2.xlsx
ABCDEFGHIJKLMNOPQR
1SamplesValue_1Value_2Data_set2Netural_set2
219-9090525
310-180180626
415360360828
559090929
614909011
72-909012
83418018014
914909015
1011909017
112909018
120-909020
131518018021
1413-909031
154-18018032
1627-36036034
1725072035
18250720
1927-720720
201114401440
21349090
2269090
2330-9090formula column
24181801801) Value 1iF(A2="","",IFS(COUNTIF(\$E\$2:\$E\$37,A2),C2,COUNTIF(\$F\$2:\$F\$37,A2),0,COUNTIF(\$E\$2:\$F\$37,"<>&X27"),C2*-1))
251-90902)Value 2iFS(A13="","",B12=0,C12,B12<0,C12*2,B12>0,90)
2681801803) column E & F : data range used in formula
272-9090
2824-180180Pls note:
2918360360I have drag down formula in B & C column as much I require upto 70 rows
3022-9090input column is A, autofill column B & C based on formula
3127-180180
32250360my question is to find
33290360
34  1**total number of data after last positive value
36
37
38  2**find zero data after last positive value
40
41  3**sum of all the negative values after last positive value
43
44  4***Last positive number
46
47
48
Sheet5
Cell Formulas
RangeFormula
B2:B48B2=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:C48C3=IFS(A3="","",B2=0,C2,B2<0,C2*2,B2>0,90)

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
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

New Member
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)

Replies
3
Views
93
Replies
1
Views
67
Replies
2
Views
39
Replies
35
Views
501
Replies
4
Views
70

1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back