not sure how to handle this if ="" formula

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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)))
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
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
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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
 

slam

Well-known Member
Joined
Sep 16, 2002
Messages
793
Ah yes, much simpler of course.

Really appreciate the help - project now finished :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,157
Members
417,011
Latest member
Amaden95

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