MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with formula?


Posted by Bill Tanner on March 29, 2001 2:58 PM

I have a fairly simple formula:

=IF(AND(G3="B",H3>1975),F3/100*0.2,IF(AND(G3="F",H3>1975),F3/100*0.3,IF(AND(G3="B",H3<1976),F3/100*0.25,IF(AND(G3="F",H3<1976),F3/100*0.35))))

And the formula works just fine. But now I want to modify it so that if the result is less than 500, then the formula returns a figure of 500.

If I go in and modify each of the IF statments, then I think I come up with a formula that is nested 8 deep and, IIRC, you can only go 7 deep. Is there another way to do this -- other than hiding the result and doing another formula in the column to the right -- a formula that then refers to the hidden cell?

Thanks much

Bill Tanner


Posted by Mark W. on March 29, 2001 3:03 PM

Checkout Aladin's handiwork...and follow suit

http://www.mrexcel.com/wwwboard/messages/13236.html

Posted by R. Shipton on March 29, 2001 4:39 PM

Alternatively.......


Alternatively, you could avoid using IF's (by using Mark W's favourite method! - i.e. Boolean logic). Something like this fairly simple formula :-

=(((G3="B")*(H3>1975)*(F3/100*0.2>500))*(F3/100*0.2))+(((G3="B")*(H3>1975)*(F3/100*0.2<=500))*500)+(((G3="F")*(H3>1975)*(F3/100*0.3>500))*(F3/100*0.3))+(((G3="F")*(H3>1975)*(F3/100*0.3<=500))*500)+(((G3="B")*(H3<1976)*(F3/100*0.25>500))*(F3/100*0.25))+(((G3="B")*(H3<1976)*(F3/100*0.25<=500))*500)+(((G3="F")*(H3<1976)*(F3/100*0.35>500))*(F3/100*0.35))+(((G3="F")*(H3<1976)*(F3/100*0.35<=500))*500)


Posted by Aladin Akyurek on March 29, 2001 6:06 PM

Re: Alternatively.......

A favorite of mine too, Boolean logic, that is. The "Boolean" formula is rather long though.

I'd suggest instead to construct a table that holds the following values:

{"B",1975,1976,0.2,0.25;"F",1975,1976,0.3,0.35}

You have 2 rows: The first should have the values up to the semi-colon; the second the rest (excluding braces).

Select these 2 rows of data and name it MULTIPLIERS via the Name Box.

Apply the following formula in some appropriate cell x:

=IF(OR(G3={"B","F"}),IF(AND(H3>VLOOKUP(G3,MULTIPLIERS,2),H3<VLOOKUP(G3,MULTIPLIERS,3)),VLOOKUP(G3,MULTIPLIERS,4),VLOOKUP(G3,MULTIPLIERS,5)),0)*(F3/100)

And next to it:

=IF(x LT 500,500,x)

I wouldn't put the preceeding 2 formulas together for you don't want to compute everything twice and produce moreover a long formula.

Replace LessThan by the appropriate symbol.

Aladin

Posted by Aladin Akyurek on March 29, 2001 6:34 PM

Or...

Bill

If you wouldn't want get rid off of your formula with 7 IFs, you could consider applying what follows in another cell:

=IF(500>x,x,500) where x is the cell ref your original formula.

Aladin

Posted by Aladin Akyurek on March 31, 2001 3:05 AM

Cannibalized formula...


The formula that follows was cannibalized by the script undelying Mr Excel's web-site:
=IF(OR(G3={"B","F"}),IF(AND(H3>VLOOKUP(G3,MULTIPLIERS,2),H3 LT VLOOKUP(G3,MULTIPLIERS,3)),VLOOKUP(G3,MULTIPLIERS,4),VLOOKUP(G3,MULTIPLIERS,5)),0)*(F3/100)


Posted by RS on March 31, 2001 4:53 PM

Too complicated?


The original question was "Is there another way to do this -- other than hiding the result and doing another formula in the column to the right -- a formula that then refers to the hidden cell?"

It seems to me that it would be simpler to 'hide the result and do another formula to the right' rather than create a table, etc. as you suggest.


Posted by Aladin Akyurek on March 31, 2001 10:49 PM

Re: Too complicated?

The formula that follows was cannibalized by the script undelying Mr Excel's web-site: =IF(OR(G3={"B","F"}),IF(AND(H3>VLOOKUP(G3,MULTIPLIERS,2),H3 LT VLOOKUP(G3,MULTIPLIERS,3)),VLOOKUP(G3,MULTIPLIERS,4),VLOOKUP(G3,MULTIPLIERS,5)),0)*(F3/100)

RIGHT.

RIGHT. IF THE IF FORMULA IS NOT GOING TO EXPAND, I'D USE THE NEXT CELL. AND NOT HIDE IT. WHY HIDE?
IF THE IF-FORMULA IS GOING TO EXPAND, A VLOOKUP FORMULA WILL BE LESS COMPLICATED.

Posted by RS on March 31, 2001 11:21 PM

Re: Too complicated?

: : The formula that follows was cannibalized by the script undelying Mr Excel's web-site: : =IF(OR(G3={"B","F"}),IF(AND(H3>VLOOKUP(G3,MULTIPLIERS,2),H3 LT VLOOKUP(G3,MULTIPLIERS,3)),VLOOKUP(G3,MULTIPLIERS,4),VLOOKUP(G3,MULTIPLIERS,5)),0)*(F3/100) The original question was "Is there another way to do this -- other than hiding the result and doing another formula in the column to the right -- a formula that then refers to the hidden cell?"
>>>> Why hide it? So that the worksheet is not cluttered with data that it is unnecessary to display.


>>>> Really? You may well think so but not eveyone would necessarily agree.


>>>> Actually, I think it is less complicated to use the single formula I posted (doesn't need any tables, hidden columns, etc.)


Posted by Aladin Akyurek on March 31, 2001 11:35 PM

Re: Too complicated?

: It seems to me that it would be simpler to 'hide the result and do another formula to the right' rather than create a table, etc. as you suggest. RIGHT. IF THE IF FORMULA IS NOT GOING TO EXPAND, I'D USE THE NEXT CELL. AND NOT HIDE IT. WHY HIDE?

I thought that that cell will be displaying the end result: either 500 or whatever result the IF returns. So I'd hide the IF, if I must hide (some) things. IF THE IF-FORMULA IS GOING TO EXPAND, A VLOOKUP FORMULA WILL BE LESS COMPLICATED.

Yes, that's what I think.

:You may well think so but not eveyone would necessarily agree.

No, I don't expect everyone will.

Aladin

Posted by Aladin Akyurek on April 01, 2001 3:49 AM

Put a MAX around...

Bill

Put a MAX around your formula:

=MAX(your-IF-formula,500)

Aladin

Posted by RS on April 01, 2001 4:18 AM

Re: Put a MAX around...

I have a fairly simple formula: =IF(AND(G3="B",H3>1975),F3/100*0.2,IF(AND(G3="F",H3>1975),F3/100*0.3,IF(AND(G3="B",H3<1976),F3/100*0.25,IF(AND(G3="F",H3<1976),F3/100*0.35)))) And the formula works just fine. But now I want to modify it so that if the result is less than 500, then the formula returns a figure of 500. If I go in and modify each of the IF statments, then I think I come up with a formula that is nested 8 deep and, IIRC, you can only go 7 deep. Is there another way to do this -- other than hiding the result and doing another formula in the column to the right -- a formula that then refers to the hidden cell? Thanks much

Posted by RS on April 01, 2001 4:19 AM

Yes! That's it!

I have a fairly simple formula: =IF(AND(G3="B",H3>1975),F3/100*0.2,IF(AND(G3="F",H3>1975),F3/100*0.3,IF(AND(G3="B",H3<1976),F3/100*0.25,IF(AND(G3="F",H3<1976),F3/100*0.35)))) And the formula works just fine. But now I want to modify it so that if the result is less than 500, then the formula returns a figure of 500. If I go in and modify each of the IF statments, then I think I come up with a formula that is nested 8 deep and, IIRC, you can only go 7 deep. Is there another way to do this -- other than hiding the result and doing another formula in the column to the right -- a formula that then refers to the hidden cell? Thanks much