Not really, it is just a simple statement of the formula, which we can read
ourselves.
What I really meant was what are you trying to do, design a rocketship, or a
mortgage calculator, that sort of question? And what are the variables that
have to be considered (rate, term etc., not K1, F17). I was hoping to gain
insight into what you were trying to do so that maybe we could suggest a
simpler, more maintainable approach.
Having said that, here is an immediate simplification.
Put this formula in a separate cell, I use J1 in my example
=IF(F170,IF(F17<4,K5,IF(F17<7,L5,IF(F17<11,M5,0)) ))
Then change your massive formula to
=IF(AND(K$2=1,H17=1,K$3<"n"),J1)+IF(AND(L$2=2,H17 =2,L$3<"N"),J2)+IF(AND(M$
2=3,H17=3,M$3<"N"),J1)+IF(AND(N$2=4,H17=4,N$3<"N "),J1)+IF(AND(O$2=5,H17=5,
O$3<"N"),J1)+IF(AND(P$2=6,H17=6,P$3<"N"),J1)+IF( AND(Q$2=7,H17=7,Q$3<"N"),
J1)+IF(AND(R$2=8,H17=8,R$3<"N"),J1)
But it does not overcome the essential problem with the formula, too ma ny
IFs, you cannot extend it as it stands. That will only be cured by a
different approach.
Just had a thought, we can get rid of the IFs by using a boolean
multiplication. Put the formula in J1 as suggested, and then use
=AND(K$2=1,H17=1,K$3<"n")*J1+AND(L$2=2,H17=2,L$3< "N")*J1+AND(M$2=3,H17=3,M
$3<"N")*J1+AND(N$2=4,H17=4,N$3<"N")*J1+AND(O$2=5 ,H17=5,O$3<"N")*J1+AND(P$
2=6,H17=6,P$3<"N")*J1+AND(Q$2=7,H17=7,Q$3<"N")*J 1+AND(R$2=8,H17=8,R$3<"N"
)*J1
This is extendible, by just adding more tests of the form
+AND(test1,test2,test3)*J1
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Jane" wrote in message
...
I wasn't able to do a print screen but here is what I am trying to do:
if k2 = "1" and h17 also = "1" and if K3 does not equal "N" AND if F17
equals either 1, 2, or 3, then give result of 18 BUT IF k2 = "1" and if
h17
also = "1" and if cell K3 does not equal "N" AND if F17 equals either 4,
5,
or 6, then give result of 12 BUT IF k2 = "1" and if h17 also = "1" and if
cell K3 does not equal "N" AND if F17 equals either 7, 8, 9, or 10, then
give
result of 6....
my absolute cells are in row 3: K3 thru T3... K3=1, L3=2, m3=3, and so
on
my other absolute cells are in row 5: K5 thru M5
'=IF(AND(K$2=1,H17=1,K$3<"n"),IF(OR(F17=1,F17=2,F 17=3),K$5)+IF(OR(F17=4,F17
=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$5 ))+
does this clarify?
"Jane" wrote:
my formula is too long and parts are repetitive but I am not sure how to
shorten it.... any help is appreciated! here it is:
'=IF(AND(K$2=1,H17=1,K$3<"n"),IF(OR(F17=1,F17=2,F 17=3),K$5)+IF(OR(F17=4,F17
=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$5 ))+IF(AND(L$2=2,H17=2,L$3<
"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F17=4,F1 7=5,F17=6),L$5)+IF(OR(F17=
7,F17=8,F17=9,F17=10),M$5))+IF(AND(M$2=3,H17=3,M$3 <"N"),IF(OR(F17=1,F17=2,F
17=3),K$5)+IF(OR(F17=4,F17=5,F17=6),L$5)+IF(OR(F17 =7,F17=8,F17=9,F17=10),M$5
))+IF(AND(N$2=4,H17=4,N$3<"N"),IF(OR(F17=1,F17=2, F17=3),K$5)+IF(OR(F17=4,F1
7=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$ 5))+IF(AND(O$2=5,H17=5,O$3
<"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F17=4,F 17=5,F17=6),L$5)+IF(OR(F17
=7,F17=8,F17=9,F17=10),M$5))+IF(AND(P$2=6,H17=6,P$ 3<"N"),IF(OR(F17=1,F17=2,
F17=3),K$5)+IF(OR(F17=4,F17=5,F17=6),L$5)+IF(OR(F1 7=7,F17=8,F17=9,F17=10),M$
5))+IF(AND(Q$2=7,H17=7,Q$3<"N"),IF(OR(F17=1,F17=2 ,F17=3),K$5)+IF(OR(F17=4,F
17=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M $5))+IF(AND(R$2=8,H17=8,R$
3<"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F17=4, F17=5,F17=6),L$5)+IF(OR(F1
7=7,F17=8,F17=9,F17=10),M$5))
|