How to find nth negative time in list?

[tezza]

Hi all

I've got a grid that is set to show the number of minutes from 3 days down to -1 day, each cell is different depending on data from another sheet.

I've worked out how to list the nth positive number from 0 mins:
=iferror(SMALL(rpd_data!$G:$G,COUNTIF(rpd_data!$G:$G,0)+1),"")
but i can't reverse it.

I need a formula that will show the nth negative time closest to 0

EG:, -2000 min, -1500 min, -1 min, -10 min, -3min

so 1st negative would show -1
5th negative would show -2000

Regards

 

[btomjack]

Try this:

=iferror(SMALL(rpd_data!$G:$G,COUNTIF(rpd_data!$G:$G,0)+(COUNTIF(rpd_data!$G:$G,"<0")+1),"")

I noticed a few things:
1. The underlined +1 part of the formula means it will return the second closest negative number to zero (-3 min, in your example above). If you remove this part, it will capture the closest number to zero (-1 min in your example above).
2. The underlined COUNTIF statement throws the formula off if zero is in the range rpd_data!$G:$G. I removed that underlined portion and still got the same results; I recommend you do the same. The final formula would be this:

=iferror(SMALL(rpd_data!$G:$G,COUNTIF(rpd_data!$G:$G,"<0"),"")

 

[tezza]

Hi, thank you for your reply

Your first formula only produces positive values, which I was finding also, not sure why at this point though.

Your second formula does grab the lowest negative, which is further than I managed to achieve, however, I will need a variable amount of negavtive value finds, not just the first, I'll probably start with a list of 10 closest negatives numbers to zero.

regards

 

[btomjack]

Try the first formula again, but switch the underlined +1 for -1, or any other number.

You could also try adding or subtracting some constant from the second formula as well, just place this after the closed parenthesis following COUNTIF(rpd_data!$G:$G,"<0")

 

[tezza]

ahh that works, here's an example of the 10th in the list

=iferror(SMALL($A$4:$U$10,COUNTIF($A$4:$U$10,1)+(COUNTIF($A$4:$U$10,"<0")-9),""))

a change at COUNTIF($A$4:$U$10,0 to COUNTIF($A$4:$U$10,1 rectified it.

Not sure why

Really appreciate your help.

Thank you.