Values list

[dtrom2002]

I have a list of values (Column A). Against each value, I want to know the last 5 values that are below zero from that row onwards.
For example:

I want queries in Columns C to G that I can drag down. As the query drags down, I want the values to appear.

If this helps, the values in column A go as far as about row 12000.

I am therefore guessing that I will end up with separate queries in columns C to G…
An example sheet is showing below


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1ST
[/TD]
[TD]2ND
[/TD]
[TD]3RD
[/TD]
[TD]4TH
[/TD]
[TD]5TH
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]-89.8
[/TD]
[TD][/TD]
[TD]-89.8
[/TD]
[TD]-89.6
[/TD]
[TD]-35.2
[/TD]
[TD]-7.4
[/TD]
[TD]-15.5
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]22.4
[/TD]
[TD][/TD]
[TD]-89.6
[/TD]
[TD]-35.2
[/TD]
[TD]-7.4
[/TD]
[TD]-15.5
[/TD]
[TD]-13.8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]52.3
[/TD]
[TD][/TD]
[TD]-89.6
[/TD]
[TD]-35.2
[/TD]
[TD]-7.4
[/TD]
[TD]-15.5
[/TD]
[TD]-13.8
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]-89.6
[/TD]
[TD]-35.2
[/TD]
[TD]-7.4
[/TD]
[TD]-15.5
[/TD]
[TD]-13.8
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]-89.6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]-35.2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]-7.4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]-15.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]27.3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi

I want to know the last 5 values that are below zero from that row onwards.

In your example you show not the last but the first 5 values that are below zero from that row onwards.
Is that what you want?

 

[dtrom2002]

Hi



In your example you show not the last but the first 5 values that are below zero from that row onwards.
Is that what you want?

no this is an example. I'd want to drag the queries down. There's around 7000 rows. Thanks

 

[pgc01]

no this is an example. I'd want to drag the queries down. There's around 7000 rows. Thanks

Sorry, if it's not the first 5 values then I don't understand.
If it's the last 5 values then they will be the same, it doesn't matter if you are at row 3 or row 500, the last 5 values that are below zero from that row onwards will always be the same.

I hope someone else understands what you need and posts a suggestion.