Take a look here please:
http://www.mrexcel.com/tip011.shtml
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.
Take a look here please:
http://www.mrexcel.com/tip011.shtml
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.
Cool, glad that helped !
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.
Thank you very much! You helped alot!
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 too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others