statistical function

[scareface]

Hello,
i have 3000 cells in Excel with different positive/negative values. I need to make a statistic what is the largest number of CONSECUTIVE negative cells. Please help me, i don't know which function to use.
Thank you
P.S. i'm using Excel 2003

[daddylonglegs]

If your cells are A1:A3000 use this formula

=MAX(FREQUENCY(IF(A1:A3000<0,ROW(A1:A3000)),IF(A1:A3000>=0,ROW(A1:A3000))))

confirmed with CTRL+SHIFT+ENTER

[scareface]

If your cells are A1:A3000 use this formula

=MAX(FREQUENCY(IF(A1:A3000<0,ROW(A1:A3000)),IF(A1:A3000>=0,ROW(A1:A3000))))

confirmed with CTRL+SHIFT+ENTER

An error occurs, my cells are form P6 to P3000, i changed the code, but it's not working
"the formula you typed contains an error..... "

Then i copied the original code you wrote and put some numbers in the cells A1:A3000, again nothing

[number_kruncher]

The formulae is an array, so it must be entered with CTRL+SHIFT+ENTER for it to work, otherwise #VALUE is displayed.

Edit the CELL and re-enter using the 3 key combination above.

[scareface]

when i pres Ctrl+Shift+Enter nothing happens. I tried this in a cell and in the formula field....

[daddylonglegs]

Hello scareface

If you select the cell with the formula, press F2 key and then hold down CTRL and SHIFT keys whilst pressing ENTER you should see curly braces like { and } appear around the formula in the formula bar....formula should then work

If this doesn’t solve your problem….

….I presume you have an “English” version of excel, some other versions require ; in formula in place of ,

[scareface]

It works now, i had to replace the 3 "," with ";". I also didn't know i was supposed to place the formula cell in the same raw as the P1:P3000. Thank you for your help! It was very useful

I have another question:
Is it possible to distribute the results as follows:
numbers of 9 NEGATIVE CONSECUTIVE values in P1:P3000 -> 126 CASES
numbers of 8 NEGATIVE CONSECUTIVE values in P1:P3000 -> 56 CASES
numbers of 7 NEGATIVE CONSECUTIVE values in P1:P3000 -> 187 CASES
numbers of 6 NEGATIVE CONSECUTIVE values in P1:P3000 -> 74 CASES
.......
numbers of 1 NEGATIVE CONSECUTIVE value in P1:P3000 -> 1983 CASES

Best Regards!

[daddylonglegs]

Hello scareface,

It shouldn’t matter where you place the formula (as long as it isn’t within your data range, i.e. P6:P3000)

To break the results down as you request requires an extra twist – a single formula, entered with CTRL+SHIFT+ENTER but in multiple cells.

If you put this formula in, for instance, cell R2

=FREQUENCY(FREQUENCY(IF(P6:P3000<0;ROW(P6:P3000));IF(P6:P3000>=0;ROW(P6:P3000)));{0;1;2;3;4;5;6;7;8;9})

Then just select the range R2:R12 [don’t try to “drag” the formula, just select the range, initially it will be blank], Press F2 then CTRL+SHIFT+ENTER

You should now see 11 values in R2:R12. You can ignore the figure in R2 (this is a count of how many times a non negative number follows another non-negative number)

R3 will give the number of cases of a single negative value
R4 will give a count of the incidence of 2 consecutive negative values
R5 will give a count of the incidence of 3 consecutive negative values
…..
R11 will give a count of the incidence of 9 consecutive negative values
R12 will give a count of the incidence of 10 or more consecutive negative values

If you want to show a single figure for 10 negative values, 11 negative values etc. then just extend the {0;1;2;3;4;5;6;7;8;9} part of the formula

[scareface]

It works perfectly! You are great, thank you for your help, i appreciate it!
Have a nice day