not sure how to handle this if ="" formula

slam

Well-known Member
Joined
Sep 16, 2002
Messages
848
Office Version
  1. 365
  2. 2019
I am looking for a ="" formula that accounts for a few different things.

I have a spreadsheet that will always have AUS in A29 and may display a corresponding time in B29. B29 has a formula that may display a blank cell if certain criteria are met.

There may be dozens of other instances of AUS in column A, and if so, these will always have a corresponding time in column B. B29 is the only one that may not have a time - if there are other instances of AUS in column A, there will always be a time in column B. It's also possible that A29 is the only instance of AUS.

I'm need a minimum formula (in B2) that will find the quickest time from column B where there is AUS in column A, but it needs to display a blank cell if B29 = "" and that's the only instance of AUS, or it will otherwise have to display the quickest time.

Hope this makes sense :)

Any help would be greatly appreciated.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This is an array formula. Confirm with Ctrl+Shift+Enter

=IF(MIN(IF(A3:A100="AUS",B3:B100))=0,"",MIN(IF(A3:A100="AUS",B3:B100)))
 
Upvote 0
Thank you again - some tweaks, and its working perfectly!

Now I'm just trying to do what you helped with in the other thread where it looks up the participant again.

I tried this:

=IF(MIN(IF(A29:A1000=A2,B29:B1000))=0,"",INDEX(C29:C1000),MATCH(B2,B29:B1000,0)))

too few arguments apparently, but I've never been good with Index/Match
 
Upvote 0
If you already found the min value in cell B2, then just test if B2="" in this formula instead of using MIN(IF

=IF(B2="","",INDEX(C29:C1000,MATCH(B2,B29:B1000,0)))

Note: no ) after C1000
 
Upvote 0
Ah yes, much simpler of course.

Really appreciate the help - project now finished :)
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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