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

#### Jqarga

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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### jasonb75

##### Well-known Member
See if this does what you need, I haven't tested it beyond your sample

Book1
ABCDEFGH
1Entity/Q2017Q12017Q22017Q32017Q42018Q1No.Q (AVG) takes a - balance to be positive (results expected)Formula?
2Entity 21,3891,6193,4391,2415,000--
3Entity 3-44,0005,0006,00011.00
4Entity 4-500,000-43,377-39,58910,50010,80033.00
5Entity 6-10,700-2,1875,78418,00720,50022.00
6Entity 7-1,145256-1,144-1502501.51.50
Sheet3
Cell Formulas
RangeFormula
H2:H6H2=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

##### New Member
Hi @jasonb75

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

1,136,314
Messages
5,675,018
Members
419,543
Latest member
Casp

### 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.

### Which adblocker are you using?

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