MINIF Function

BHomestar

Board Regular
Joined
Nov 17, 2003
Messages
88
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
Rich (BB 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)))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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