long formula

J

Jane

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 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 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,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))+ ... etc.
see full formula below:

'=IF(AND(K$2=1,H17=1,K$3<>"n"),IF(OR(F17=1,F17=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))+IF(AND(L$2=2,H17=2,L$3<>"N"),IF(OR(F17=1,F17=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))+IF(AND(M$2=3,H17=3,M$3<>"N"),IF(OR(F17=1,F17=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))+IF(AND(N$2=4,H17=4,N$3<>"N"),IF(OR(F17=1,F17=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))+IF(AND(O$2=5,H17=5,O$3<>"N"),IF(OR(F17=1,F17=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))+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(F17=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,F17=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(F17=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(F17=7,F17=8,F17=9,F17=10),M$5))+IF(AND(t$2=10,H17=10,t$3<>"N"),IF(OR(F17=1,F17=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))

THANK YOU IN ADVANCE FOR YOUR HELP!! jane
 
J

Jane

I should add that I have the first part of the formula "repeated" 9 times for
a total of ten... I shortened it to a total of 8 and it works...it's adding
the 2 "repeats" that gets me into trouble. the following section of the
formula remains the same throughout
IF(OR(F17=1,F17=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))

if there a way to consolidate this piece of the formula?
jane
 
W

William

Hi Jane

If I understood you correctly, try the following (all one line).

=IF(OR(K2<>1,H17<>1,$K$3="N"),0,
IF(AND(INT(F17)>=1,INT(F17)<=3),$K$5,
IF(AND(INT(F17)>=4,INT(F17)<=6),$L$5,
IF(AND(INT(F17)>=7,INT(F17)<=10),$M$5,
0))))


--
XL2002
Regards

William

(e-mail address removed)

| 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 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 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,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))+ ... etc.
| see full formula below:
|
|
'=IF(AND(K$2=1 said:
"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR
Click to expand...
|
(F17=4,F17=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,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))+IF(AND(N$2=4,H17=4,N$3<>"N"),IF(OR(F17
=1,F17=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))+IF(AND(O$2=5,H17=5,O
$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(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(F17=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
,F17=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(
F17=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(F17=7,F17=8,F17=9,F17=10)
,M$5))+IF(AND(t$2=10,H17=10,t$3<>"N"),IF(OR(F17=1,F17=2,F17=3),K$5)+IF(OR(F1
7=
| 4,F17=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F17=10),M$5))
|
| THANK YOU IN ADVANCE FOR YOUR HELP!! jane
|
| "Jane" wrote:
|
| >
 
W

William

Hi Jane

I wouldn't worry about it - change the formula to...

IF(OR(K2<>1,H17<>1,$K$3="N"),0,IF(AND(F17>=1,F17<=3),$K$5,IF(AND(F17>=4,F17<
=6),$L$5,IF(AND(F17>=7,F17<=10),$M$5,0))))

--
XL2002
Regards

William

(e-mail address removed)

| thanks! I'll try that ps..... what does INT stand for?
|
| "Jane" wrote:
|
| >
 
J

Jane

well, I wasn't able to get that to work. would it be easier if I sent the
spreadsheet or is there a way to do a print screen to post here so you have a
clearer sense of what I'm trying to do? I tried to do that earlier with no
luck. It's surprising that isn't possible in this site since functions can
be hard to visualize at times...
 
W

William

Hi Jane

Send me the file but please ensure that the subject line says "Excel" else I
will not receive your email. Please do not post the file on this ng.

--
XL2002
Regards

William

(e-mail address removed)

| well, I wasn't able to get that to work. would it be easier if I sent the
| spreadsheet or is there a way to do a print screen to post here so you
have a
| clearer sense of what I'm trying to do? I tried to do that earlier with no
| luck. It's surprising that isn't possible in this site since functions
can
| be hard to visualize at times...
|
| "William" wrote:
|
| > Hi Jane
| >
| > I wouldn't worry about it - change the formula to...
| >
| >
IF(OR(K2<>1,H17<>1,$K$3="N"),0,IF(AND(F17>=1,F17<=3),$K$5,IF(AND(F17>=4,F17<
| > =6),$L$5,IF(AND(F17>=7,F17<=10),$M$5,0))))
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | thanks! I'll try that ps..... what does INT stand for?
| > |
| > | "Jane" wrote:
| > |
| > | >
| >
| >
| >
| >
 
B

Bob Phillips

Jane,

This suggestion will not work as you want to go beyond 8 IF tests, which is
a problem which is why I asked for details. I have replied in your previous
thread, and I think I have the solution for you, but you need to test it.

If you can't get at the previous thread, post back here and I will re-post.

--

HTH

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formula too long, not sure how to shorten 16
Workday function returns the wrong date when the duration is 5 day 5
Showing the top result from a range of cells 6
Arrays and scattered cell values or another way? 16
fourmla with result no less than assigned value 1
Formula Errors 2
Linking two IF statements together 2
Adding a Function to a cell when inserting a row from a macro 0

Top