Avatar of wrt1mea
wrt1mea asked on

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.
Microsoft Excel

Avatar of undefined
Last Comment
hypercube

8/22/2022 - Mon
ASKER
wrt1mea

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.
Juan Ocasio

Do you have an example or can you provide more detail?

"I need to add "CLOSING"" <-Under what circumstance?
ASKER
wrt1mea

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
wrt1mea

See the attached example.
EX-EXCH-3-14-14.xlsx
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
hypercube

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.
ASKER
wrt1mea

I worked the EE on the original formula...

Yes, your formula works exactly the way I need to, in  a much simpler form. Thanks!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
hypercube

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.