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?
 
Ejgil HedegaardCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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.