IF Formula with Exception

[Dandelion3]

Hi everyone.

I have an IF formula in place for a file that i'm working on. Final value is based on a percentage, but there is one person whose final value should be based on a dollar value instead. The current formula conditions apply no matter what the user name is, but there is one person who is an exception, so I need to make adjustments to basically say "All else applies, but for this particular user, it should be something different" while keeping everything else in place. I still want everything on the same tab. Hopefully that makes sense. Do i just add a comma & put that IF statement on the end? Will that change everything else?

 

[6StringJazzer]

You are going to have to show us your current IF formula, explain any ranges it refers to, and tell us what cell/column the names are in. Then we have to know where to get the dollar value from.

 

[Dandelion3]

You are going to have to show us your current IF formula, explain any ranges it refers to, and tell us what cell/column the names are in. Then we have to know where to get the dollar value from.

Attached is the current formula in place (in column Z) and should apply to all owners, but there is one owner who has a different structure. With what you see here, no matter what the name (owner) is, cell Z should be a % based of what the classification is in column B...... but for this one owner who is an exception, column B should be a dollar value. So *using dummy numbers here*, If John Doe (the exception) is listed in column E and the classification is "New Logo" the modifier should be $20. If the term (Column R) for John Doe is 1 year it should be $5, 2 years, $10, 3 years or greater, $15. I want to include this exception for him and keep everything else the same for any other names.

 

[bob33]

Is there a column entry for John Doe that makes him unique, ie, "New Logo"?
If statements rely on comparing content of cells to guide how the formula works; an indicator cell would help drive the formula to the right result.

 

[dreid1011]

Something along these lines, wrapping your current formula in another IF to test the owner name:

=IF(E2="John Doe",$ amount formula, IF(B2="Channel Driven/New Logo",80%,IF(B2="New",100%,IF(B2="New Logo",150%,IF(B2="Channel Driven",70%,IF(B2="Resale",80%,0))))))

 

[Dandelion3]

Is there a column entry for John Doe that makes him unique, ie, "New Logo"?
If statements rely on comparing content of cells to guide how the formula works; an indicator cell would help drive the formula to the right result.

No, just his name in the user column. Everything else is the same. The classification modifier for his commission payout is just based on a set value (depending on contract terms & category) rather than a percentage of an amount.

 

[Dandelion3]

Something along these lines, wrapping your current formula in another IF to test the owner name:

=IF(E2="John Doe",$ amount formula, IF(B2="Channel Driven/New Logo",80%,IF(B2="New",100%,IF(B2="New Logo",150%,IF(B2="Channel Driven",70%,IF(B2="Resale",80%,0))))))

That makes sense, but how would I write the formula for different instances? I mean If E2 = John Doe AND R2 = 36, AND B2 = New Logo, then the the value should be $50?

Hopefully that makes sense

 

[dreid1011]

That makes sense, but how would I write the formula for different instances? I mean If E2 = John Doe AND R2 = 36, AND B2 = New Logo, then the the value should be $50?

Hopefully that makes sense

You just copy and rewrite your current formula to account for whatever $ amounts you want:

=IF(E2="John Doe",IF(B2="Channel Driven/New Logo",26,IF(B2="New",33,IF(B2="New Logo",50,IF(B2="Channel Driven",23,IF(B2="Resale",26,0))))), IF(B2="Channel Driven/New Logo",80%,IF(B2="New",100%,IF(B2="New Logo",150%,IF(B2="Channel Driven",70%,IF(B2="Resale",80%,0))))))

 

[Dandelion3]

That makes sense, but how would I write the formula for different instances? I mean If E2 = John Doe AND R2 = 36, AND B2 = New Logo, then the the value should be $50?
or IF E2 = John Doe AND R2 = 24 AND B2 = New Channel, then the value should be $25

Hopefully that makes sense

 

[dreid1011]

Does column R only come into play with John Doe or all other owners too? And what does column R do? Is it a multiplier? Or some other code to determine $ amount?