Convert the first negative value to months and 0 to subsequent negative value, else convert the next negative value to months

[jns1087]

Hi Guys,


Can i ask for help with this, I'm playing with this formula and can't get it done properly. me and chatgpt are running in circles for hours, anyway.

I have this formula that i want to convert the first negative source value (Column C) into months from data on Col. B, then the subsequent negative values (Column C) will display only as 0, the only thing that the subsequent value will change when the first negative value becomes positive.

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H
Month	Date	Source Value	Value Converted month	Goal: Put the month value for the first negative source value, and 0 to subsequent negative values, else if the first negative value became positive it will turn into 0, then the next negative value will result to its corresponding month	Result	Subsequent Result if first negative value turn to positive	Subsequent Result if second negative value turn to positive
Jan	1/1/2023​	1	0	1	0	0	0
Feb	2/1/2023​	-50	2	0	2	0	0
Mar	3/1/2023​	0	0	3	0	0	0
Apr	4/1/2023​	2	0	4	0	0	0
May	5/1/2023​	3	0	5	0	0	0
Jun	6/1/2023​	5	0	6	0	0	0
Jul	7/1/2023​	-100	7	0	0	7	0
Aug	8/1/2023​	0	0	8	0	0	0
Sep	9/1/2023​	-200	9	0	0	0	9
Oct	10/1/2023​	0	0	10	0	0	0
Nov	11/1/2023​	6	0	11	0	0	0
Dec	12/1/2023​	7	0	12	0	0	0
			=IF(SUM(C2)<0,MONTH(B2),0)	=IF(C2<0,0,MONTH(B2))			​

thanks,
jns

 

[KOKOSEK]

Like this?

Book1
	A	B	C	D	E	F	G
1	Month	Date	Source value	result	Subsequent	Subsequent	Subsequent
2	January	01/01/2023	1	0	0	0	0
3	February	01/02/2023	-50	2	0	0	0
4	March	01/03/2023	0	0	0	0	0
5	April	01/04/2023	2	0	0	0	0
6	May	01/05/2023	3	0	0	0	0
7	June	01/06/2023	5	0	0	0	0
8	July	01/07/2023	-100	0	7	0	0
9	August	01/08/2023	0	0	0	0	0
10	September	01/09/2023	-200	0	0	9	0
11	October	01/10/2023	0	0	0	0	0
12	November	01/11/2023	6	0	0	0	0
13	December	01/12/2023	-300	0	0	0	12
Sheet1

Cell Formulas
Range	Formula
D2:G13	D2	=IF(COUNTIFS($C$2:C2,"<0")=COLUMN()-3,IF($C2<0,MONTH($B2),0),0)

 

[jns1087]

Like this?

Book1
	A	B	C	D	E	F	G
1	Month	Date	Source value	result	Subsequent	Subsequent	Subsequent
2	January	01/01/2023	1	0	0	0	0
3	February	01/02/2023	-50	2	0	0	0
4	March	01/03/2023	0	0	0	0	0
5	April	01/04/2023	2	0	0	0	0
6	May	01/05/2023	3	0	0	0	0
7	June	01/06/2023	5	0	0	0	0
8	July	01/07/2023	-100	0	7	0	0
9	August	01/08/2023	0	0	0	0	0
10	September	01/09/2023	-200	0	0	9	0
11	October	01/10/2023	0	0	0	0	0
12	November	01/11/2023	6	0	0	0	0
13	December	01/12/2023	-300	0	0	0	12
Sheet1

Cell Formulas
Range	Formula
D2:G13	D2	=IF(COUNTIFS($C$2:C2,"<0")=COLUMN()-3,IF($C2<0,MONTH($B2),0),0)

yes it works on my expectation! i dont know how that happen

 

[jns1087]

Like this?

Book1
	A	B	C	D	E	F	G
1	Month	Date	Source value	result	Subsequent	Subsequent	Subsequent
2	January	01/01/2023	1	0	0	0	0
3	February	01/02/2023	-50	2	0	0	0
4	March	01/03/2023	0	0	0	0	0
5	April	01/04/2023	2	0	0	0	0
6	May	01/05/2023	3	0	0	0	0
7	June	01/06/2023	5	0	0	0	0
8	July	01/07/2023	-100	0	7	0	0
9	August	01/08/2023	0	0	0	0	0
10	September	01/09/2023	-200	0	0	9	0
11	October	01/10/2023	0	0	0	0	0
12	November	01/11/2023	6	0	0	0	0
13	December	01/12/2023	-300	0	0	0	12
Sheet1

Cell Formulas
Range	Formula
D2:G13	D2	=IF(COUNTIFS($C$2:C2,"<0")=COLUMN()-3,IF($C2<0,MONTH($B2),0),0)

thank you again!!!