And/if formula not working - faulty logic on my part suspected!

I've got a nice juicy formula here. So what I want to happen is this..if the entry in the entry in the Deferred % column is ANYTHING but 100% or 0% and the revenue date 2 is no, I should get a calculation. If the Deferred% is 100% or 0%, or the REvenue date 2 is no, I should get 0. At the moment I am testing this on a deferred % with 100% and I am getting a number. I'm now not seeing it clearly but I'm guessing I am screwed up in the And logic bit of the formula. Thanks. !

.=IFERROR(IF(AND([@[Deferred %]]<100%,[@[Deferred %]]=0%,[@[Revenue Date2]]="no"),0,IF(AND([@[S&M  Contract Start date]]=[@[Revenue Date]],),"([@Amount]*(1-[@[Deferred %]])+[@Amount]/[@[Revenue Days]]",[@Amount]/[@[Revenue Days]])),0%)
LVL 1
agwalshAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
I think you need an OR statement in there as well...

AND(OR(Deferred=100%,Deferred=0%),RevDate="No")
0
 
agwalshAuthor Commented:
OK, how's this...
=IFERROR(IF(AND(OR([@[Deferred %]]=100%,[@[Deferred %]]=0%),[@[Revenue Date2]]="no"),0,IF(AND([@[S&M  Contract Start date]]=[@[Revenue Date]],),"([@Amount]*(1-[@[Deferred %]])+[@Amount]/[@[Revenue Days]]",[@Amount]/[@[Revenue Days]])),0%).
Still getting an actual amount there grrrr
0
 
Rob HensonFinance AnalystCommented:
Check the two values that are checked to ensure that they are:

Rate - Exactly 100% or 0%
Date2 - no  (case shouldn't be an issue)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
agwalshAuthor Commented:
@Rob Henson - will do that..thanks a mil. Ah, it's always the simple things isnt' it? :_)
0
 
agwalshAuthor Commented:
the 100% was fine - the file kept crashing and not updating the values...but that was the solution. Thanks as always.
0
 
agwalshAuthor Commented:
As always when you are down a rabbit hole, come to Experts Exchange - and Rob Henson in particular :-) to sort the problem.
0
 
Rob HensonFinance AnalystCommented:
Thank you for the great feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.