formula drag down problem

ACCA369

New Member
Joined
Dec 27, 2020
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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
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
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)
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 27, 2020
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top