MINIF Function

[BHomestar]

Hey All!

In the below formula I am trying to return the minimum value in column AH that has the criteria of B37 in column G. The formula is not properly finding the values in column G that meet criteria B37. Could someone help me to fix this?

=--(INDEX(Pricer!G:G,C$4):INDEX(Pricer!G:G,D$4)=B37)--MIN(INDEX(Loans!AH:AH,C$4):INDEX(Loans!AH:AH,D$4))

Thanks!!

Bryan H.

 

[mleosu]

Yes, that works, so in theory I would assume I could put an OFFSET statement around it to get a value from the same row as the MINIF found.

But it doesnt work.

=OFFSET(CELL("Address",INDEX('C3''s'!$J$1:$J$1693,MATCH(Q73,IF('C3''s'!$F$1:$F$1693=B73,'C3''s'!$J$1:$J$1693),0))),0,-6)

 

[Aladin Akyurek]

Yes, that works, so in theory I would assume I could put an OFFSET statement around it to get a value from the same row as the MINIF found.

But it doesnt work.

=OFFSET(CELL("Address",INDEX('C3''s'!$J$1:$J$1693,MATCH(Q73,IF('C3''s'!$F$1:$F$1693=B73,'C3''s'!$J$1:$J$1693),0))),0,-6)

Try rather something like...

=INDEX('C3''s'!$D$1:$D$1693,MATCH(Q73,IF('C3''s'!$F$1:$F$1693=B73,'C3''s'!$J$1:$J$1693),0))

which needs to be confirmed with control+shift+enter.

 

[mleosu]

Genius! Thanks!

Guess I need to work more with INDEX!

 

[Aladin Akyurek]

Genius! Thanks!

You are welcome. Thanks for providing feedback.

Guess I need to work more with INDEX!

Yep. INDEX is an important function.

 

[Excelestial]

If you ever want an ingenius answer....ask the ingenius Aladin!

 

[josexcell]

Hello all, I am not sure if it is appropriate to respond here to a follow up question, but here goes.

I have tried the suggestions in this thread. The CSE tip page was fantastic. One clarification I would like is as follows: I tried this in my workbook. I input the formula in sheet 2, while my data resides in sheet 1. The first formula worked fine. The rest did not, they were off by small amounts. {{at this point I had run an =Average(If( formula}}.
Then, after not getting anywhere for a while, I went to Sheet1 to enter the formulas there. Doing this made all of the formulas (after copying) work just fine.
Is this what the Local vs. Global, etc. above refers to when using CSE formulas?

Thanks for any clarification.

 

[Aladin Akyurek]

Hello all, I am not sure if it is appropriate to respond here to a follow up question, but here goes.

I have tried the suggestions in this thread. The CSE tip page was fantastic. One clarification I would like is as follows: I tried this in my workbook. I input the formula in sheet 2, while my data resides in sheet 1. The first formula worked fine. The rest did not, they were off by small amounts. {{at this point I had run an =Average(If( formula}}.
Then, after not getting anywhere for a while, I went to Sheet1 to enter the formulas there. Doing this made all of the formulas (after copying) work just fine.
Is this what the Local vs. Global, etc. above refers to when using CSE formulas?

Thanks for any clarification.

In post #6, local refers to a definition that will be usable only on the sheet where the definition is constructed. Global used for the fact that the definition is usable on all of the sheets in the workbook.

 

[jeffsang]

I'm having trouble with the following array formula:


=MIN(IF(A2:A5=("*"&TEXT(CONCATENATE("; ",D1,";"),)&"*"),B2:B5))


Where:


Column A Column B
Artist Year
; Johnny Cash; 1965
; Johnny Cash; 1975
; Johnny Cash; Carl Perkins; 1959
; Bob Dylan; 1962


and cell D1 = Johnny Cash


As part of another COUNTIF formula, I've placed semicolons before and after every artist in column A so that excel doesn't mistake instances of "Bonnie 'Prince' Billy" for "Prince;" then use the TEXT and CONCATENATE formulas so it recognizes the semicolons as well as instances when Johnny Cash collaborated with someone else. However though these formulas work fine separately, I just get a zero result when I pull all together. Can anyone let me know what I'm doing wrong? Thanks!

 

[Aladin Akyurek]

@jeffsang

Control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(SEARCH(TEXT(CONCATENATE("; ",D1,";"),),A2:A5)),B2:B5))

Even better:

=MIN(IF(ISNUMBER(SEARCH("; "&D1&";",A1:A4)),B2:B5))

You might even want to try MINIFS if you are on a 2016 system.

 

[jeffsang]

Thanks Aladin for the quick reply. That worked perfectly!