nested formulas

[wgreen]

I am trying to join a series of nested formulas and don't know if that is possible. Of course I have hit the limit and need to join 2 sets of nested formulas if possible. If someone would have any ideas please let me know. What I am doing is checking data in 2 cells and then computing a percentage based on the data inputed. This is my work in progress so far...
=IF(AND(X16=1,Y16="G"),0.045,IF(AND(X16=1,Y16="VG"),0.055,IF(AND(X16=1,Y16="E"),0.065,IF(AND(X16=2,Y16="G"),0.035,IF(AND(X16=2,Y16="VG"),0.045,IF(AND(X16=2,Y16="E"),0.055,IF(OR(Y16="RI",Y16="U"),0))))))))

I need to join the additional criteria with the difference being X16 becomes equal to 3 and 4 and goes through the same Y criteria.

Perhaps there is an easier way other than if statements???


[ This Message was edited by: wgreen on 2002-02-19 05:59 ]

[Juan Pablo González]

Well, it's really hard to answer with something specific without the "specifics".

In one word, YES, it is possible.

[Aladin Akyurek]

On 2002-02-19 05:27, wgreen wrote:
I am trying to join a series of nested formulas and don't know if that is possible. Of course I have hit the limit and need to join 2 sets of nested formulas if possible. If someone would have any ideas please let me know. What I am doing is checking data in 2 cells and then computing a percentage based on the data inputed. This is my work in progress so far...
=IF(AND(X16=1,Y16="G"),0.045,IF(AND(X16=1,Y16="VG"),0.055,IF(AND(X16=1,Y16="E"),0.065,IF(AND(X16=2,Y16="G"),0.035,IF(AND(X16=2,Y16="VG"),0.045,IF(AND(X16=2,Y16="E"),0.055,IF(OR(Y16="RI",Y16="U"),0))))))))

I need to join the additional criteria with the difference being X16 becomes equal to 3 and 4 and goes through the same Y criteria.

Perhaps there is an easier way other than if statements???


[ This Message was edited by: wgreen on 2002-02-19 05:59 ]

Make a 3-column list of the values of interest (which your IF formula is referring to) in some unused range, say, in F1:H6 like the one that follows:

{1,"G",0.045;
1,"VG",0.055;
1,"E",0.065;
2,"G",0.035;
2,"VG",0.045;
2,"E",0.055}

Use the following formula (instead of a long IF formula) to retrieve the value of interest from the above list, given the values in X16 and Y16:

=INDEX(H1:H6,SUMPRODUCT(MATCH(X16&"@"&Y16,F1:F6&"@"&G1:G6,0)))

Aladin