I need to update an excel formula

Good morning...

I need to update the following formula:

=IF($C10="CLOSED",1,(OR($C10="ROUTING FOR CLOSE",$C10="CLOSING")+0))

I need to add "CLOSING"

I also need to add if C10 = "APPROVED" it returns the value in AB10, which will be a number.
LVL 1
wrt1meaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ejgil HedegaardConnect With a Mentor Commented:
The formula is confusing.
The logic is
If C10="CLOSED" then return 1
If C10="ROUTING FOR CLOSE" or C10="CLOSING" then return 1
Else return 0

The part (OR($C10="ROUTING FOR CLOSE",$C10="CLOSING")+0 converts TRUE/FALSE to 1/0.

So everything except APPROVED returns 1, and APPROVED shall return value in column AB.
So formula will be.
=IF($C10="APPROVED",$AB10,1)

If that is not true, please explain the logic.
0
 
wrt1meaAuthor Commented:
Sorry for the confusion, i was mixing some things up....I see that closing is in the formula.

Just need to add:  If C10 = "APPROVED" it returns the value in AB10, which will be a number.
0
 
Juan OcasioApplication DeveloperCommented:
Do you have an example or can you provide more detail?

"I need to add "CLOSING"" <-Under what circumstance?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
wrt1meaAuthor Commented:
Disregard adding the "closing" part. Sorry for the confusion as I am kind of scatter brained this morning.

I just need to add to the formula if C10 = Approved, it returns the value in AB10
0
 
wrt1meaAuthor Commented:
See the attached example.
EX-EXCH-3-14-14.xlsx
0
 
Fred MarshallPrincipalCommented:
Like hgholt, I often write or diagram Excel formual logic.  Here is how I'd write your current formula so it's more readable / understandable:

=IF($C10="CLOSED",1,(OR($C10="ROUTING FOR CLOSE",$C10="CLOSING")+0))

IF $c10="CLOSED"
    THEN 1
    ELSE ($C10="ROUTING FOR CLOSE") OR ($C10="CLOSING") +0

The last expression might be written:
    ELSE IF ( ($C10="ROUTING FOR CLOSE") OR ($C10="CLOSING"),1,0)

Now, you want to add:
IF C10 = "Approved",AB10,........

Now, if you don't want to change anything else and want this added item to override everything else then you could write:

 =IF(C10="APPROVED", AB10,"IF($C10="CLOSED",1,(OR($C10="ROUTING FOR CLOSE",$C10="CLOSING")+0)))

This would look like this:

IF $C10="APPROVED"
   THEN AB10
   ELSE
   IF $c10="CLOSED"
      THEN 1
      ELSE ($C10="ROUTING FOR CLOSE") OR ($C10="CLOSING") +0

Written this way the APPROVED  test overrides everything else.
0
 
wrt1meaAuthor Commented:
I worked the EE on the original formula...

Yes, your formula works exactly the way I need to, in  a much simpler form. Thanks!
0
 
Fred MarshallPrincipalCommented:
Well, without trying to interpret the text names like "CLOSING", I see that the possibility for a zero outcome would be lost using the form:
=IF($C10="APPROVED",$AB10,1)

Presumably you want the *zero* outcome when $C10 is not "APPROVED and when C10 is not "CLOSED" and is neither "ROUTING FOR CLOSE" nor "CLOSING".  

But then, I don't know what the full set of values for C10 might be.
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.