Solved

# I need to update an excel formula

Posted on 2014-03-14
261 Views
Good morning...

I need to update the following formula:

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

I also need to add if C10 = "APPROVED" it returns the value in AB10, which will be a number.
0
Question by:wrt1mea
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 1

Author Comment

ID: 39929427
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

LVL 14

Expert Comment

ID: 39929437
Do you have an example or can you provide more detail?

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

LVL 1

Author Comment

ID: 39929455
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

LVL 1

Author Comment

ID: 39929533
See the attached example.
EX-EXCH-3-14-14.xlsx
0

LVL 22

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 39929700
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

LVL 26

Expert Comment

ID: 39929822
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)

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

LVL 1

Author Closing Comment

ID: 39929851
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

LVL 26

Expert Comment

ID: 39929957
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month4 days, 2 hours left to enroll