# How many days a number has been negative

#### Pault22

##### New Member
Hi, I'm self tought in Excel

I have a sheet of prices going back 30 days, the prices change everyday, some numbers are negative and some are positive.
I want to know, if a number is negative, how many consecutive days, up until today (current day) has it been negative for?

Thanks for the help
Paul

#### jasonb75

##### Well-known Member
It's working fine for me, are all the ranges in the formula set to the same cells?

{=IF(ISNA(MATCH(0,IF(B1:AE1<0,1,0)+IF(B1:AE1>100,1,0),0)-1),COUNT(B1:AE1),MATCH(0,IF(B1:AE1<0,1,0)+IF(B1:AE1>100,1,0),0)-1)}

Should values of 100 be included or just values above 100?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Pault22

##### New Member
yes, can values of 100 be included? sorry if it's a hassle?

Thanks so much for all the help!!

I'm gonna try this formula again

#### jasonb75

##### Well-known Member
Try

{=IF(ISNA(MATCH(0,IF(B1:AE1<0,1,0)+IF(B1:AE1>=100,1,0),0)-1),COUNT(B1:AE1),MATCH(0,IF(B1:AE1<0,1,0)+IF(B1:AE1>=100,1,0),0)-1)}

Think that should cover all possibilities now

#### Pault22

##### New Member
my apologies! my numbers are in percentages! so it DOES work when i change the 100 to a %! rookie error!

Thanks so much bru! you've helped me so much today at work! who know's how long i would've been at it alone!
Much appreciated!
Have a great day

#### pgc01

##### MrExcel MVP
Hi

Assuming numbers in columns C:L and the results in column A, in A1:

=MIN(IF((C1:L1>=0)*(C1:L1<100),COLUMN(C1:L1)-COLUMN(C1),COUNT(C1:L1)))

This is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

Copy down

#### Pault22

##### New Member
awsome thanks

The previos 1 seems to be working - so i'll stick with that, but i'll also keep this 1 on my sheet, just in case..

Now I just gotta figure out how i'm gonna automate it to update for new days and then link the answers up to my final info sheet.

Thanks again for all your help with this!

Best regards
Paul

Replies
1
Views
83
Replies
0
Views
92
Replies
3
Views
127
Replies
13
Views
233
Replies
1
Views
250