how to create a conditional formula for a range of cells?

[Irene]

I am trying to make a conditional formula from 7 range of cells. The two has
different condition, and the remaining five has the same condition. I tried
doing it individually, but when it came to the seventh and last cell, it
doesnt accept it. Is it because I can only do so much function in a
formula(it says not more than seven)? If so, is there any way I can just do
the last five cells at once since the condition is all the same for the last
five range of cells? This is my what I was trying to do:

=if(g5=k52,rounded(sum(k22:k25)*80%,0),if(g5=k53,rounded(sum(k22:k25),0),if(g5=k54,rounded(sum(k22:k25)*85%,0), if....so on and so forth up to k58

From k54 to k58, the conditions are the same which is 85% of the total of
k22 to k25 rounded off.

Please help.

Irene

 

[Bob Phillips]

Irene,

Here is an example with 3 conditions, plug the other 4 in a similar
construct

=ROUND(SUM(K22:K25)*(1-((G5=K52)*80%))*(1-((G5=K53)*0%))*(1-((G5=K54)*85%)),
0)

--

HTH

RP
(remove nothere from the email address if mailing direct)

I am trying to make a conditional formula from 7 range of cells. The two has
different condition, and the remaining five has the same condition. I tried
doing it individually, but when it came to the seventh and last cell, it
doesnt accept it. Is it because I can only do so much function in a
formula(it says not more than seven)? If so, is there any way I can just do
the last five cells at once since the condition is all the same for the last
five range of cells? This is my what I was trying to do:

=if(g5=k52,rounded(sum(k22:k25)*80%,0),if(g5=k53,rounded(sum(k22:k25),0),if(
g5=k54,rounded(sum(k22:k25)*85%,0), if....so on and so forth up to k58

 

[DOR]

Use L52:L58 or any other available 7-cell range to hold your multipliers
(0.8,1,0.85,0.85,...etc.) corresponding to the K52:K58 values, then use

=ROUND(SUM(k22:k55)*INDEX(L52:L58,MATCH(g5,k52:k58,0)),0)

HTH