IF AND Troubles!

[jeffexceler]

Hello, I'm hoping someone can help because I've scratched my head pretty hard on this one. I'm trying to do an if function for modeling subscription fees for a software company. The hard part comes when the three subscription levels release in different months. I need to show what percentage of customers will adopt certain subscription levels BUT not if the subscription level hasn't even been released yet. I hope that makes sense and I've attached a screenshot to help. The below formula is producing a $1 result but should be about $237. Can someone help?

Thank You!!

Jeff

=((IF(AND(H15=0,H16=0),BG6,(IF(AND(H15>0,H16=0),BH6,(IF(AND(H16>0),BI6)))*((IF(H$2>=$BA14,$BA14<>0))*((H4-G4)*$C14*$B14)*H11)))))

Screen Shot 2013-05-21 at 1.57.32 PM.jpg

[scott.s.fower]

=((IF(AND(H15=0,H16=0),BG6,(IF(AND(H15>0,H16=0),BH6,(IF(AND(H16>0),BI6)))*((IF(H$2>=$BA14,$BA14<>0))*((H4-G4)*$C14*$B14)*H11)))))

((IF(H$2>=$BA14,$BA14<>0)) <--this bit here doesn't make sense as an if statement, it is saying if H$2 is greater than or equal to $BA$14 then $BA$14 is not equal to 0, I don't think that computes with excel, excel will give you a true or false answer so, if it is false, then it will = 0 and if it is true it will = 1 which will give you the value of ((IF(AND(H15=0,H16=0),BG6,(IF(AND(H15>0,H16=0),BH6,(IF(AND(H16>0),BI6))) as your answer, if H15 and H16 both equal 0 then it will result in BG6, if BG6 = 1 then that is why you get 1 as your answer

[scott.s.fower]

=((IF(AND(H15=0,H16=0),BG6,(IF(AND(H15>0,H16=0),BH6,(IF(AND(H16>0),BI6)))*((IF(H$2>=$BA14,$BA14<>0))*((H4-G4)*$C14*$B14)*H11)))))

((IF(H$2>=$BA14,$BA14<>0)) <--this bit here doesn't make sense as an if statement, it is saying that if H$2 is greater than or equal to $BA$14 then $BA$14 is not equal to 0, I don't think that computes with excel, excel will give you a true or false result from this statement,so, if it is false, then it will = 0 and if it is true it will = 1 which will give you the value of ((IF(AND(H15=0,H16=0),BG6,(IF(AND(H15>0,H16=0),BH6,(IF(AND(H16>0),BI6))) as your answer, if H15 and H16 both equal 0 then it will result in BG6, if BG6 = 1 then that is why you get 1 as your answer. I'm guessing that BG6 is 100% (meaning 1) equal to Basic, 1 offering on your table, hope that is a clear answer (probably not)

[scott.s.fower]

sorry, double posted, the second one is the complete answer

[jeffexceler]

Forget this post....

[jeffexceler]

Ok so I get what you are saying about why it would equal 1, since H15 and H16 =0......but why doesn't the formula multiple 100% (which is BG6) times the right side of the equation? For instance, if I just erased the left side of the equation and only left ((IF(H$2>=$BA14,$BA14<>0))*((H4-G4)*$C14*$B14)*H11), it calculates just fine with a result of $267.65