Hi Guys
Need a little help with this one.
=IF(C8=TRUE,IF(C12=TRUE,IF(OR(P5=TRUE,P6=TRUE),IF(C18=TRUE,D8,D8),IF(OR(P5=TRUE,P6=TRUE),IF(C16=TRUE,D8,D8),IF(C16=TRUE,IF(C18=TRUE,D8*3,D8*2),D8),IF(C14=TRUE,IF(OR(C16=TRUE,C18=TRUE),IF(OR(P5=TRUE,P6=TRUE),D8*2,D8*4),D8*2))))))
I can’t get the last argument to work, if there is a simpler way of making this work please show me how.
Thanks
Holy crap. Sorry, that just slipped out.
Anyway, you can only have up to 7 nested IF statements (there are workarounds like this one: http://www.cpearson.com/excel/nested.htm).
It appears you're testing and re-testing various cells to see if they're TRUE, and based on which ones are TRUE you're returning either D8, D8*2, D8*3 or D8*4.
Can you write out the rules you want to obey and post a copy of your spreadsheet (in .zip format)? Unless someone else can wrap their head around this logic...???
I do not know what logic you are trying to accomplish, but 2 things seem evident to me:
1) 2 of the if statements yield the same result for true or false, so why are they needed?
2) It does not seem like all of the if statements false conditions have been defined.
not a professional, just trying to assist.....
Hi
Cut down C8
Single door C12
Double door C14
Sidelite 1 C16
Sidelite 2 C18
Depending on the size of the door P5: P6 is full glass.
What I need is this:
IF C8 = TRUE AND C12 = TRUE THEN D8
IF C8 = TRUE, C12 = TRUE, C16 = TRUE THEN D8*2
IF C8 = TRUE, C12 = TRUE, C16 = TRUE, C18 = TRUE THEN D8*3
IF C8 = TRUE, C14 = TRUE THEN D8*2
IF C8 =TRUE, C14 = TRUE, C16 = TRUE, C18 = TRUE THEN D8*4
Now if full glass is selected then:
IF C8 = TRUE, IF(OR (P5=TRUE,P6=TRUE), C12 =TRUE THEN D8
IF C8 = TRUE, IF(OR (P5=TRUE,P6=TRUE), C12 = TRUE, C16 =TRUE THEN D8
IF C8 = TRUE, IF(OR (P5=TRUE,P6=TRUE), C12=TRUE, C16 = TRUE, C18 =TRUE THEN D8
IF C8 = TRUE, IF(OR (P5=TRUE,P6=TRUE), C14 = TRUE THEN D8*2
IF C8 = TRUE, IF(OR (P5=TRUE,P6=TRUE),C14 = TRUE, C16 = TRUE, C18 =TRUE THEN D8*2
I suggest that you split this down into (at least) 2 stages,
one based on C8,C12,C14,C16 & C18 and the other on P5 & P6
Perhaps you can assign an index to each of the posible combinations of C8,C12,C14,C16 & C18, thus :
IF C8 = TRUE AND C12 = TRUE THEN 1
IF C8 = TRUE, C12 = TRUE, C16 = TRUE THEN 2
etc.
this index can then be passed to different CHOOSE() functions based on the values of P5 & P6.
Am I correct in thinking that when C12 is TRUE than C14 must be FALSE and visa versa? does this allow some simplification.
All your list seems to have C8=TRUE, what happens if C8=FALSE
Note that you dont have to use the IF(C12=TRUE,...,...) construction, if C12 is a logical value then you can just use IF(C12,...,...)
you can string together many conditions like this
=AND(C8=TRUE,C12=TRUE,C16=TRUE,C18=TRUE)*D8*3+AND(C8=TRUE,C12=TRUE,C16=TRUE,C18=FALSE)*D8*2+AND(C8=TRUE,C12=TRUE,C16=FALSE)*D8+AND(C8=TRUE,C12=FALSE,C14=TRUE,C16=TRUE,C18=TRUE)*D8*4+AND(C8=TRUE,C12=FALSE,C14=TRUE,C16=FALSE,C18=FALSE)*D8*2
as long as your conditions are unique you won't add results.
This eliminates the # of if statements from being a limit, you might run into the length of the formula being a limit though - but you could solve that by adding together 2 cells with only pieces of the formula in each
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks