# IF AND Troubles!

1. ## IF AND Troubles!

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  Register To Reply

2. ## Re: IF AND Troubles!

=((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  Register To Reply

3. ## Re: IF AND Troubles!

=((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)  Register To Reply

4. ## Re: IF AND Troubles!

sorry, double posted, the second one is the complete answer  Register To Reply

5. ## Re: IF AND Troubles!

Forget this post....  Register To Reply

6. ## Re: IF AND Troubles!

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  Register To Reply