| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
long formula
|
|
#2
|
|||
|
|||
|
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,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))+ ... etc. see full formula below: '=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,F17=5,F17=6),L$5)+IF(OR(F17=7,F17=8,F17=9,F 17=10),M$5))+IF(AND(M$2=3,H17=3,M$3<"N"),IF(OR(F1 7=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)+I F(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(F1 7=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)+I F(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(O R(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(F 17=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 "Jane" wrote: |
|
#3
|
|||
|
|||
|
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,F1 7=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 "Jane" wrote: |
|
#6
|
|||
|
|||
|
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 "Jane" wrote in message ... | thanks! I'll try that ps..... what does INT stand for? | | "Jane" wrote: | | |
|
#7
|
|||
|
|||
|
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 "Jane" wrote in message ... | thanks! I'll try that ps..... what does INT stand for? | | "Jane" wrote: | | |
|
#8
|
|||
|
|||
|
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 "Jane" wrote in message ... | 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 | | | | "Jane" wrote in message | ... | | thanks! I'll try that ps..... what does INT stand for? | | | | "Jane" wrote: | | | | | | | | |
|
#9
|
|||
|
|||
|
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) "William" wrote in message ... 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 "Jane" wrote in message ... | thanks! I'll try that ps..... what does INT stand for? | | "Jane" wrote: | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL ERROR - series formula is too long | Excel Worksheet Functions | |||
| revert formula insertion to old method | Setting up and Configuration of Excel | |||
| "formula too long" error message | Excel Discussion (Misc queries) | |||
| formula too long, not sure how to shorten | Excel Worksheet Functions | |||
| Help with macro formula and variable | Excel Worksheet Functions | |||
Linear Mode
