Time greater than

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
Im trying to writer a code to pull all names that have a start time of 11 or greater cell B2 has the following code

Code:
=IFERROR(INDEX('Bid Results'!$X$6:$X$253,SMALL([B]IF('Bid Results'!$Z$6:$Z$253=>TIME(11,0,0)[/B],ROW('Bid Results'!$Z$6:$Z$253)-ROW('Bid Results'!$Z$6)+1),ROWS('Bid Results'!$Z$6:'Bid Results'!$Z6))),"")

What im notice the code is doing is that is recognizing all the "OFF" times as Greater than or equal too 11 Im needing it to Ignore all cells that have "OFF"
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Time is numeric and any text is greater than any number, for this to work correctly you need to eliminate any non-numeric values from the array first.
This formula should do it, array confirmation is not needed.
PHP:
=IFERROR(INDEX('Bid Results'!$X:$X,AGGREGATE(15,6,ROW('Bid Results'!$Z$6:$Z$253)/(VALUE('Bid Results'!$Z$6:$Z$253)>=TIME(11,0,0)),ROWS(Z$6:Z6))),"")
 
Last edited:
Upvote 0
So, on your sheet Bid Results in column Z some of the rows have OFF rather than a time and your array formula is evaluating those cells as greater than 11 and returning the rows as a result.

did I understand you correctly?

and your example did not show as an array but you are using Ctrl+Shift+Enter correct?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top