Help with formula?

Checkout Aladin's handiwork...and follow suit

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

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)

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

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

Cannibalized formula...

The formula that follows was cannibalized by the script undelying Mr Excel's web-site:

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.

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.

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.)

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

Put a MAX around...

Bill

Put a MAX around your formula:

=MAX(your-IF-formula,500)

Aladin

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

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