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.

I need to update the following formula:

=IF($C10="CLOSED",1,(OR($C

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

Juan Ocasio

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

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

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

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

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

wrt1mea

See the attached example.

EX-EXCH-3-14-14.xlsx

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

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.

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 questionhypercube

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.

=IF($C10="CLOSED",1,(OR($C

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,(

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.

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!

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.

=IF($C10="APPROVED",$AB10,

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.

Just need to add: If C10 = "APPROVED" it returns the value in AB10, which will be a number.