Calculate number columns (quarters) that takes a value to change from - to +

[Jqarga]

Hello guys,

This query is quite specific to a model I'm putting in place but I hope you can provide me with a solution to make it work.

With the table below, I'm trying to come up with a formula that gives me the avg number of quarters that takes a balance to switch from negative to positive. How can I accomplish that?

Any suggestion would be much appreciated. Many thanks!

Entity/Q	2017Q1	2017Q2	2017Q3	2017Q4	2018Q1	No.Q (AVG) takes a - balance to be positive (results expected)	Formula?
Entity 2	1,389	1,619	3,439	1,241	5,000	-
Entity 3			-44,000	5,000	6,000	1
Entity 4	-500,000	-43,377	-39,589	10,500	10,800	3
Entity 6	-10,700	-2,187	5,784	18,007	20,500	2
Entity 7	-1,145	256	-1,144	-150	250	1.5

 

[jasonb75]

See if this does what you need, I haven't tested it beyond your sample

Book1
	A	B	C	D	E	F	G	H
1	Entity/Q	2017Q1	2017Q2	2017Q3	2017Q4	2018Q1	No.Q (AVG) takes a - balance to be positive (results expected)	Formula?
2	Entity 2	1,389	1,619	3,439	1,241	5,000	-	-
3	Entity 3			-44,000	5,000	6,000	1	1.00
4	Entity 4	-500,000	-43,377	-39,589	10,500	10,800	3	3.00
5	Entity 6	-10,700	-2,187	5,784	18,007	20,500	2	2.00
6	Entity 7	-1,145	256	-1,144	-150	250	1.5	1.50
Sheet3

Cell Formulas
Range	Formula
H2:H6	H2	=IFERROR(AVERAGE(IFERROR(1/(1/FREQUENCY(IF(B2:F2<0,COLUMN(B2:F2)),IF(B2:F2>0,COLUMN(B2:F2)))),"")),"-")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jqarga]

Hi @jasonb75

That actually worked! Thanks a lot for the quick response.