Lesser of two cells that meet multiple criteria...

**micorge** · 12-02-2012, 09:32 PM

Hello, I need help trying to figure out the proper way to enter a formula that will show the results of two other cells based on whichever is "lesser" and also meets two other criteria.

These are the attempts I have been working on but still not able to function properly.

---------------------------------------------------------------
=IF(I7<5000,I7,IF(I5<5000,I5,IF(I7>5000,"5000.00",IF(I5>5000,"5000.00",IF("","")))))

=IF(F7>5000,F7,IF(F7<5000,"5,000.00",IF("","")))

=IF(F7>5000,"5,000",IF(F7<5000,F7,IF("","")))

=+IF(OR(K12>0),IF(AND($L$8="Yes"),K12,""),"")

=+IF(I7="","",IF(I5<I7,IF(AND(I5>5000,"5000.00",I5),IF(OR(I7<I5,(AND(I7>5000,"5000.00",I7)))))))

=+IF((I7="",""),IF(OR(I7<5000,I7<I5,I7>5000,"5000.00",I7),IF(OR(I5<5000,I5<I7,I5>5000,"5000.00",I5)))))

---------------------------------------------

Essentially we need the one main cell to only display the results of whichever out of the two other cells, "I7" and "I5" are lesser than the other and only if it is under "5,000", if the amounts are both over "5,000" than we need the main cell to display a max of "5,000". And of course, if there is nothing displayed in either cells than we want the main cell to not show anything, "If nothing , do nothing" as well.

I hope I explained that clear enough.

Thank you all for your help with this if you can figure it out... it's been driving me crazy.

- George

**vlady** · 12-02-2012, 09:53 PM

maybe this one if i understand it correctly. other wise can you rephrase the conditions on a better way
something like

if I7 and I5 > 50000 then 50000
if ....

Formula:

Please Login or Register  to view this content.

**dredwolf** · 12-02-2012, 10:00 PM

try something like:
=If( I7=I5, result if true,If(And( I7>I5,I7>5000),result if true,IF(And(I5>I7,I5>5000),result if true,result if false)))

Hope this helps

**micorge** · 12-03-2012, 02:19 AM

vlady, thank you very much! You're amazing! I wasn't aware of the "MIN" functions in excel... your formula WORKED!!!

=IF(SUM(I5,I7)=0,"",IF(AND(I5>5000,I7>5000),5000,MIN(I5,I7)))

I love this site.