I will also try to include a small print screen if that will help....give me
a few minutes... and I do appreciate the help!
"Bob Phillips" wrote:
I think it would be better to describe it here for two reasons
1) it would make you think about what is actually going on so as to be able
to put it into words (and looking at the formula, I think this would be a
good idea)
2) other NG watchers will see it and you will get a greater potential input
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Jane" wrote in message
...
could I send my spreadsheet so you can see what I anm trying to
accomplish?
"Bob Phillips" wrote:
Jane,
The way I would do would be to break it down with intermediate
calculations,
such as =OR(F17=1,F17=2,F17=3) and then refer to them, or to create some
workbook names (InsertNamedefine Name ...), such as
F17_123 with a value of =OR(F17=1,F17=2,F17=3)
F17_456 with a value of =OR(F17=4,F17=5,F17=6) and
F17_78910 with a value of =OR(F17=7,F17=8,F17=9,F17=10)
make sure the formula cell is selected when you create these, and then
substitute each occurrence of that value with the workbook name.
By very cursory testing suggests that you will still have a problem
though,
as it threw out an error.
But there must be a better way than this formula. What is it trying to
do?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Jane" wrote in message
...
CORRECTION - THIS is the formula that is too long and I'm not sure how
to
shorten it.... thanks in advance!! jane
'=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))+IF(AND(s$2=9,H17=9,s $3<"N"),IF(OR(F17=1,F17=2
,F17=3),K$5)+IF(OR(F17=4,F17=5,F17=6),L$5)+IF(OR(F 17=7,F17=8,F17=9,F17=10),M
$5))+IF(AND(t$2=10,H17=10,t$3<"N"),IF(OR(F17=1,F1 7=2,F17=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))
"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))
|