Min Max IF

[Bill Sender]

hi all.

Having an issue with this formula. Can someone help out. Thanks

IF(C12<=E10,MIN(F10,(C24*1296/90*C27/H13),IF(C12<=C10,MIN(D10,(C24*1296/90*C27/H13)))),0)

What this should mean is:
If C12 is less than or equal to E10 then the lower number of F10 or the equation should appear...but if C12 is equal to or less than C10 (but higher than E10) then the lower number of D10 or the equation should appear.

equationC24*1296/90*C27/H13)

[Jdevil]

IF(C12<=E10,MIN(F10,(C24*1296/90*C27/H13)),IF(C12<=C10,MIN(D10,(C24*1296/90*C27/H13)),0))

try this perhaps?

[Bill Sender]

Jdevil, Its not working. It gives a value of 0 if entered lower number and False if larger number..

[newdoverman]

Jdevil's formula works as per your description.

[ben_hensel]

Jdevil's syntax is correct.

What should the correct answer be with the cases you've given?

Eg,
C24=C27=0 ??
H13=0 => Formula will return #DIV/0! error message.
What are F10 and D10?
Is D10 = FALSE? (which is equal to zero when assessed arithmetically).


EDIT:

Like newdoverman's pointed out, when you have two independent criteria like this, you have four possible outcomes.

I find it's usually helpful to tabulate:

	C12 <= E10	C12 > E10
C12 <= C10	MIN(F10, fxn)	0
C12 > 10	MIN(F10, fxn)	0

That's what your formula is driving towards logically, regardless of syntax errors; is that what you want?

[loginjmor]

Hi -

I just tried Jdevil's solution and it appears to work. What do you mean by "It gives a value of 0 if entered lower number and False if larger number"? Can you give us the numbers in those cells that make it not work and what you think the answer should be? Better yet, can you post a sample spreadsheet with a couple of examples of how it's not working?

[Bill Sender]

YOU MADE IT MY FRIEND! Thank you so much! Very appreciate your help!

[Bill Sender]

Hi all,

A few changes to this equation have been made. C12 is now (C15/N17)and the equation has changed.

My only question left on this function is: Is it possible to have 3 options in the MIN Function (as seen below with B35,C35,F10)?

=IFERROR(IF((C15/N17)<=E10,MIN(IF((C15/N17)<=E10,MIN(B35,C35,F10))),MIN(IF(AND((C15/N17)<=C10,(C15/N17)>E10),MIN(B35,C35,D10)))),"")*N17

Thanks!

[loginjmor]

Hi -

Yes, MIN() can evaluate multiple items in a list. In this case it should take the smallest value of B35,C35 and F10.