How to find nth negative time in list?

tezza

Board Regular
Joined
Sep 10, 2006
Messages
165
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

btomjack

Board Regular
Joined
Sep 13, 2013
Messages
229
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

Board Regular
Joined
Sep 10, 2006
Messages
165
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

Board Regular
Joined
Sep 13, 2013
Messages
229
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

Board Regular
Joined
Sep 10, 2006
Messages
165
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,884
Members
414,413
Latest member
Sinbin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top