G
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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
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
G
Guest
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
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:
|
|
(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:
|
| >
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
(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:
|
| >
G
Guest
thanks! I'll try that ps..... what does INT stand for?
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:
|
| >
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:
|
| >
G
Guest
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...
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:
| > |
| > | >
| >
| >
| >
| >
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)
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.