Page 1 of 2 12 LastLast
Results 1 to 10 of 17

MINIF Function

This is a discussion on MINIF Function within the Excel Questions forums, part of the Question Forums category; Hey All! In the below formula I am trying to return the minimum value in column AH that has the ...

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Location
    NJ
    Posts
    88

    Default MINIF Function

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default Re: MINIF Function

    Take a look here please:

    http://www.mrexcel.com/tip011.shtml
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Location
    NJ
    Posts
    88

    Default Re: MINIF Function

    Juan Pablo González,

    Thank you very much for the link to CSE formula's. I have asked the board before about writing formula's like SUMPRODUCTIF that are addressed by enabling the CSE function. I have always received excellant advice from this board on the one-off questions, and now reading the link on CSE's has helped me to understand the whole countif, sumif, CSE relationship for the big picture probably eliminating the need to clog up the board with questioning in the future. I successfully wrote the MINIF function that I originally asked the board about.

    Thank You!!

    Bryan H.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default Re: MINIF Function

    Cool, glad that helped !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    Aug 2011
    Posts
    2

    Default Re: MINIF Function

    Hello.

    Does CSE Formula work in Excel 2003?

    I followed the instructions, trying to use a MINIF formula, but all I got as a result is #NUM!.

    I wrote the formula as follows:
    =MIN(IF($T:$T=$W2,$L:$L))
    where $W2 is the constant I'm looking for in $T:$T and $L:$L holds the numeric values.

    Am I doing something wrong?

    Thanks.

    Eyal.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,756

    Default Re: MINIF Function

    Quote Originally Posted by eyalinbar View Post
    Hello.

    Does CSE Formula work in Excel 2003?

    I followed the instructions, trying to use a MINIF formula, but all I got as a result is #NUM!.

    I wrote the formula as follows:
    =MIN(IF($T:$T=$W2,$L:$L))
    where $W2 is the constant I'm looking for in $T:$T and $L:$L holds the numeric values.

    Am I doing something wrong?

    Thanks.

    Eyal.
    On 2003, CSE formulas are not allowed to reference whole columns. That's why you get #NUM!. This limit has been lifted up on 2007 version and later.

    On 2003 you need to use definite ranges or dynamic (named) ranges...

    1] Definite ranges...

    =MIN(IF($T$2:$T$400=$W2,$L$2:$L$400))

    2] Dynamic ranges...

    Local:

    In W1 enter...

    =MATCH(9.99999999999999E+307,L:L)

    The formula becomes, still CSE...

    =MIN(IF($T$2:INDEX($T:$T,$W$1)=$W2,$L$2:INDEX($L:$L,$W$1)))

    Global:

    Assuming that the data is on Sheet1...

    Define Lrow by means of Insert | Name | Define as referring to:

    =MATCH(9.99999999999999E+307,Sheet1!$L:$L)

    Define Trange (or any other more convenient name) as referring to:

    =Sheet1!$T$2:INDEX(Sheet1!$T:$T,Lrow)

    and Lrange as referring to:

    =Sheet1!$L$2:INDEX(Sheet1!$L:$L,Lrow)

    The formula now becomes, still CSE...

    =MIN(IF(Trange=$W2,Lrange))

    These names can be referred to from anywhere in the current workbook.

    An additional option...

    Convert the current area with headers into a List by means of Data | List | Create List and let the formula simply refer to the current ranges. The List functionality adjust the formula automatically whenever the data area itself changes.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Aug 2011
    Posts
    2

    Default Re: MINIF Function

    Thank you very much! You helped alot!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,756

    Default Re: MINIF Function

    Quote Originally Posted by eyalinbar View Post
    Thank you very much! You helped alot!
    You are welcome. Thanks for providing feedback.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Nov 2006
    Location
    texas
    Posts
    29

    Default Re: MINIF Function

    SO GLAD to find this!!! However, I am stuck with one thing. I am using the Min function... =MIN(IF('C3''s'!$F$1:$F$1693=COMPARISON!B73,'C3''s'!$J$1:$J$1693)) and it gives me the minimum of the items, BUT I also want (in another column) to report where it came from (which contract the MIN is on).

    I tried to use offset, but that didnt work, since it's not returning a reference.
    So I tried to create a reference, but that doesnt seem to work either.

    Any ideas?

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,756

    Default Re: MINIF Function

    Quote Originally Posted by mleosu View Post
    SO GLAD to find this!!! However, I am stuck with one thing. I am using the Min function... =MIN(IF('C3''s'!$F$1:$F$1693=COMPARISON!B73,'C3''s'!$J$1:$J$1693)) and it gives me the minimum of the items, BUT I also want (in another column) to report where it came from (which contract the MIN is on).

    I tried to use offset, but that didnt work, since it's not returning a reference.
    So I tried to create a reference, but that doesnt seem to work either.

    Any ideas?
    Assuming that the formula you have in C73 on COMPARISON...

    D73, control+shift+enter, not just enter:
    Code:
    =CELL("Address",INDEX('C3''s'!$J$1:$J$1693,
      MATCH(C73,IF('C3''s'!$F$1:$F$1693=B73,
       'C3''s'!$J$1:$J$1693),0)))
    Assuming too much and qualifying too much are two faces of the same problem.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com