How many days a number has been negative

[Pault22]

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

 

[VaraK]

Hi,

Assuming your prices are in Col A to Col AD.

=COUNTIF(A1:AD1,"<"&0)

Hope this helps.

 

[Pault22]

Hey, thanks for your reply

but that seems to just give me the amount of negative numbers in the whole row of numbers.
Here's what i'm trying to do: take this row of numbers:

-5, -8, -4, 3, 9, 6, 3, 8, 8, -2, -4, -8, -9

Now the row is full of negative & positive numbers (and my sheet usually patterns like that - few days will be negative, then few days positive)

I want to kno if the numbers are negative, How many days have they been negative for? therefore 3 days in the above example.

Thanks alot
Paul

 

[VaraK]

In your post, there are 7 numbers that are negative. How did you say 3 days are negative?

 

[Pault22]

I only want to know what the latest, consecutive number of negative days is. Not how many negative days in total there are.

It's for my work (and there's alot of other factors first), but If i see that 1 of todays number's is negative - it plays a big role how long that number has been negative for (ie: how many days has this number been negative for)

So in the above row it's just the latest string of consecutive negative numbers = 3 days have been negative

 

[Pault22]

sorry - i dont know if i've explained this right?
This might be better:

21 June 20 June 19 June 18 June 17 June 16 June 15 June 14 June
-3 -2 -8 5 4 3 -5 -8


If todays 21st June - How long's the row been negative for: 3 days (incl 21st)

So, firstly, only if the number's negative - then I want to know how long has it been negative for.

 

[Pault22]

21 June 20 June 19 June 18 June 17 June 16 June 15 June 14 June
-3 -2 -8 5 4 3 -5 -8

 

[Pault22]

each number goes under each date above

 

[jasonb75]

Try,

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	-5	-8	-4	3	9	6	3	8	8	-2	-4	-8	-9
2
3	3
Sheet1

 

[VaraK]

Hi

I think, this should work:

=MATCH(0,A1:AE1,1)