Link to home
Start Free TrialLog in
Avatar of agwalsh
agwalsh

asked on

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%)
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of agwalsh
agwalsh

ASKER

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
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)
Avatar of agwalsh

ASKER

@Rob Henson - will do that..thanks a mil. Ah, it's always the simple things isnt' it? :_)
Avatar of agwalsh

ASKER

the 100% was fine - the file kept crashing and not updating the values...but that was the solution. Thanks as always.
Avatar of agwalsh

ASKER

As always when you are down a rabbit hole, come to Experts Exchange - and Rob Henson in particular :-) to sort the problem.
Thank you for the great feedback.