Hi.
I have these 4 functions that i want to be able to "run" in a nested IF where the functions are used in this manner:
=if((funct1 or funct2 or funct3 or funct4);"OK";"")
funct1: =IF(AND(B7=1;D7=1;E7=1);"OK";"")
funct2: =IF(AND(B7=0;C7=1;F7=1;A7<10000);"OK";"")
funct3: =IF(AND(B7=0;C7=1;G7=1;A7<25000);"OK";"")
funct4: =IF(AND(B7=0;C7=1;H7=1;A7<50000);"OK";"")
Best regards
Nicolai
Last edited by Vestlink; 11-09-2009 at 07:41 AM. Reason: SOLVED
Nicolai, regards the F7:H7 - will a 1 always exist in this range (when B7=0 and C7=1) ?
If so can more than 1 ever exist in the range - ie F7 & H7 = 1 ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
The answer is yes to this one.Originally Posted by DO
The answer is no to this one. only 1.
Nicolai
Last edited by DonkeyOte; 11-09-2009 at 07:11 AM. Reason: added quotes for ease of reference...
thanks... couple more I should have asked -- just trying to ascertain the particulars so as to try and keep formula as lean as possible...
Q1: Can B7 be anything other than 1/0 ?
Q2: If B7 is 0 can C7 be anything other than 1 ?
q1: no. only 1/0
q2: only 1.
Nicolai
OK, based on my interpretations then... perhaps
(I'm assuming the syntax for the inline array is correct for your client locale - if not you will need to tell me where you're located)
Please Login or Register to view this content.
Last edited by DonkeyOte; 11-09-2009 at 07:27 AM. Reason: typo - 50000 rather than 5000
Hi.
tnx alot. I'll give it a spin.
Located in Norway, Oslo
Nicolai
I believe in Norway you would use
{10000\25000\50000}
for the inline array as opposed to
{10000;25000;50000}
as utilised in earlier example.
THNX ALOT.
I'll kiss your feet!
Nicolai
N.B.
How would i tweak it to read "Not OK" if conditions are not met?
Nicolai
Quite a few routes in truth, one could be
Please Login or Register to view this content.
smoking!
And if the initial values is blank and not "#N/A"?
Nicolai
Well by my reckonining there should always be a min of 3 digits in range covering B:H, is that correct ?
If so
=IF(COUNT(B7:H7)<3;"";existing formula)
If not, ie more convoluted, then you could revert to either/or below pending XL version:
XL2007:
=IFERROR(function;"")
Pre XL2007
=LOOKUP(REPT("Z";255);CHOOSE({1\2};"";existing formula))
hahaha
Beauty!
Yes.. i surely did the trick
Thnx again
see.
That is what i want to be able to do.
What road would be smart to travel if one is to become as gifted as you?
Any suggestions?
Nicolai
Thank you, though you say gifted whereas many would say cursed...
Well, I've been down several (ie many dead-ends!) ... unfortunately there's no XL/VBA Sat Nav to guide us... very much a case of making as many mistakes as you can as early on as you can as that is how we learn what's good and what's not so good.What road would be smart to travel if one is to become as gifted as you?
Hang around on forums like this one and you will pick up lots of tips & tricks... the questions are varied (mostly) so there is scope to learn quite a lot in a short space of time (I'd say).
another question:
What if I accidentially enter Zero in both b7 and c7?
The result would be "OK"
Is there any way to avoid that so i would get the "not OK" in return?
Nicolai
Last edited by Vestlink; 11-10-2009 at 05:42 AM.
i'll stick around to suck up the knowlede
Nico
I'm not quite sure which approach you used for the final formula but the original
could perhaps be converted toPlease Login or Register to view this content.
Please Login or Register to view this content.
tnx again.
Did the trick.
Now its up to me to comprehend the formula, but it's fairly clear.
Nicolai
Here is what I got so far:
=HVIS(ANTALL(B7:H7)<3;"";INDEKS({"Ikke godkjent"\"OK"};1+HVIS(B7=1;OG(D7=1;E7=1);C7*(A7<=INDEKS({10000\25000\50000};SAMMENLIGNE(1;F7:H7;0))))))
But I (or my friend) want to expand the functionality so this matrix is fullfilled:
IF P1=1 P3=1 P4=1 ok
IF P1=0 P2=1 P5=1 B <=10 000 ok
P1=0 P2=1 P6=1 B <=25 000 ok
P1=0 P2=1 P7=1 B <=50 000 ok
B P1 P2 P3 P4 P5 P6 P7
1 0 0 1 ok
1 0 0 2 ok
0 0 1 0 Not ok
0 0 1 1 Not ok
0 0 1 2 Not ok
0 1 0 0 ok
0 1 0 1 ok
0 1 0 2 ok
Can that be done in this formula or do I have to look for something else?
Best regards
Nicolai
It would be a great deal easier if you were to post a sample file (and outline desired results based on sample data). A file will also cater for translations etc...
But offcause!
What was i thinking. Good point!
Nicolai
Nicolai, sorry I'm not quite sure what I'm mean to be looking at in the attached... for ex. there are no values in the bottom table (and no B values in the middle table).
Put in some values and expected outputs and from that I and others should be able to determine the logic.
Remember - I've not looked at this for weeks so though it may still be obvious to you it's not going to be obvious to me...
Thanks
i have tried to fill in info in the file..
Nicolai
I know I sound like a bore but can you elaborate in terms of the significance of B1:E4, A10:E17 ? Which represents your truth table ? (or are both relevant).
It's still unclear to me I'm afraid.
If it's unclear it's all my fault!
Line 1-17 represent the conditions that have to met in order to return OK/NOT ok in the matrix from line 20 and on
Nicolai
I confess I'm lost ... I'm not sure how all the rules tie out in terms of B requirements, you now mention value 2 in P4 ... seemingly P5:P7 are irrelevant for most ?
I would make one suggestion... build a full blow truth table [P1:P7] for every combination that is "ok" and denote for which of those combinations the value of B also becomes relevant (eg P1=0,P2=1,P5=1) and where B is relevant specify the max value (eg 10000)
I think only when you've done that will it become clear(er)
i will see to that my friend fills it out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks