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. 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. Re: MINIF Function

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

3. 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.

5. 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. Re: MINIF Function

Originally Posted by eyalinbar
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.

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.

7. Re: MINIF Function

Thank you very much! You helped alot!

8. Re: MINIF Function

Originally Posted by eyalinbar
Thank you very much! You helped alot!
You are welcome. Thanks for providing feedback.

9. 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. Re: MINIF Function

Originally Posted by mleosu
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)))```

Page 1 of 2 12 Last

Posting Permissions

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