MrExcel Publishing
Your One Stop for Excel Tips & Solutions

NESTED IF STATEMENT


Posted by DIANNE on June 26, 2000 7:53 AM

I AM TRYING TO WRITE A FORMULA TO GET THE FOLLOWING
RESULTS AND AM NOT THAT PROFICIENT IN NESTED IF STATEMENTS.
I WOULD APPRECIATE ANY ASSISTANCE. THANK YOU.

IF A1 IS LESS THAN OR EQUAL TO B1, THEN THE RESULT IS A1,
OTHERWISE THE RESULTS EQUALS B1. HOWEVER IF THE RESULTING
FIGURE FROM A1 OR B1 IS $5,000 OR GREATER THEN THE RESULT
MUST BE $5,000 REGARDLESS OF THE ACTUAL FIGURE SITTING
IN CELL A1 OR B1.

IN OTHER WORDS THE RESULT IS THE LESSER OF A1 OR B1
UP TO A MAXIMUM OF $5,000.


Posted by JAF on June 27, 0100 12:54 AM

Dianne

The following will give you the lower number in cells A1 and B1 up to a maximum of £5000
=IF(AND(B1<=A1,B1<5000),B1,IF(AND(A1<=B1,A1<5000),A1,5000))


JAF

Posted by JAF on June 27, 0100 5:54 AM

Simpler Formula - Same Result

Dianne

A simpler formula to return what you need is:

=IF(MIN(A1:B1)>5000,5000,MIN(A1:B1))

Hope this helps.


JAF

Posted by Ryan on June 26, 0100 8:18 AM

Hope this helps you. It worked fine for me. Let me know.
Ryan

=IF(A1<=B1,IF(A1>5000,5000,A1),IF(B1>5000,5000,B1))