Need another expert on formula?

Not so formula savvy · 07/03/24

Scenario,

need to edit an existing column formula to exclude any $$$ (greater or less than 0) scenarios for a specific column value from a different column ("TYPE") is "Cost Avoidance" or "Optimization" so it does not produce a "YES".

So for e.g.

The Column "Type" has the following values:

New Contract

NPR

Cost Avoidance

Optimization

The Column "Implemented Top 10" formula:

=IF([IMP $]@row > 0, "YES", "")

must produce a "YES" for any row greater than "0" - no matter what "Type" value.

I need to produce a "YES" for any row greater than "0" but exclude those $$$ (negative or positive) dollar amounts with a "Type" value that is "Cost Avoidance" or "Optimization".

let me know if that made sense or is possible?

(

YES Column formula 1.PNG

YES Column formula 2.PNG

see attachments)

Paul Newcome · 07/08/24

Give this a try:

=IF(AND(Type@row <> "Cost Avoidance", Type@row <> "Optimization", [IMP $]@row > 0, Status@row <> "Dropped", Status@row <> "Hold"), "YES")

dojones · 07/04/24

Try this.

=IF(OR(Type@row = "Optimization", Type@row = "Cost Avoidance"), "", IF([IMP $]@row > 0, "Yes", ""))

Not so formula savvy · 07/08/24

Oops, I forgot to factor in another couple values from an additional column that i need to factor in:

In addition to the scenario above, I also need to factor in ONLY those rows with a "Status" of:

Active

Completed

Validated

(see attachment)…

fx 3.PNG

Paul Newcome · 07/08/24

Give this a try:

=IF(AND(Type@row <> "Cost Avoidance", Type@row <> "Optimization", [IMP $]@row > 0, Status@row <> "Dropped", Status@row <> "Hold"), "YES")

Not so formula savvy · 07/08/24

You both came through for me…yes it worked…🤩…and thanks once again Paul!!!!

Paul Newcome · 07/09/24

Happy to help. 👍️

Need another expert on formula?

Best Answer

Answers

Help Article Resources

Categories