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?

 

[Dandelion3]

Can you provide some sample values and what you expect the result to be in AA?


With the values I used, what is the result you expect?

It should be a $ value and not a percentage.

So with the last picture I sent you (attached again) of the commission pay structure, $1,318.75 should've been the final actual value in AA for John Doe, and below is the break down.

OPP MRR: $275
MRR Renewal: $0
MRR Achievement Bracket: 25%

so 25% of $275 = $68.75 + New Logo $500 + 3 year contract $750

 

[dreid1011]

Does this produce expected results for both rows?

Book1 12-18-2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		Classification			Owner									Opp MRR	Opp aMRR	Opp NRR	COMP MRR	Initial Term	Case Commission	Renewal Commission	Contract (Years)	Accelerator Base	Accelerator Renewal	Base - Total Commission	Renewal - Total Commission	Classification Modifier	Total Commission
2		New Logo			John Doe									275				36	275	0	3	0	0	275	0	1	1318.75
3
4		Channel Driven												275			0	24	275	0	2	41.25	0	316.25	0	0.7	221.375
Sheet1

Cell Formulas
Range	Formula
S2,S4	S2	=IF(N2>0,N2,0)
T2,T4	T2	=Q2
U2,U4	U2	=R2/12
V2,V4	V2	=IF(E2<>"John Doe",IF(U2=1,0,IF(U2=2,S2*15%,IF(U2=3,S2*35%,IF(U2>=4,S2*50%,0)))),0)
W2,W4	W2	=IF(U2=1,0,IF(U2=2,T2*15%,IF(U2=3,T2*30%,IF(U2>=4,T2*50%,0))))
X2,X4	X2	=S2+V2
Y2,Y4	Y2	=W2+T2
Z2,Z4	Z2	=IF(E2<>"John Doe",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))))),1)
AA2,AA4	AA2	=IF(E2="John Doe",SUM(N2*LOOKUP(N2,{0,5001,7501,10001},{0.25,0.5,0.75,1}),IF(B2="New Logo",500,0),MIN(U2*250,750)),SUM(X2:Y2)*Z2)

 

[Dandelion3]

Does this produce expected results for both rows?

Book1 12-18-2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		Classification			Owner									Opp MRR	Opp aMRR	Opp NRR	COMP MRR	Initial Term	Case Commission	Renewal Commission	Contract (Years)	Accelerator Base	Accelerator Renewal	Base - Total Commission	Renewal - Total Commission	Classification Modifier	Total Commission
2		New Logo			John Doe									275				36	275	0	3	0	0	275	0	1	1318.75
3
4		Channel Driven												275			0	24	275	0	2	41.25	0	316.25	0	0.7	221.375
Sheet1

Cell Formulas
Range	Formula
S2,S4	S2	=IF(N2>0,N2,0)
T2,T4	T2	=Q2
U2,U4	U2	=R2/12
V2,V4	V2	=IF(E2<>"John Doe",IF(U2=1,0,IF(U2=2,S2*15%,IF(U2=3,S2*35%,IF(U2>=4,S2*50%,0)))),0)
W2,W4	W2	=IF(U2=1,0,IF(U2=2,T2*15%,IF(U2=3,T2*30%,IF(U2>=4,T2*50%,0))))
X2,X4	X2	=S2+V2
Y2,Y4	Y2	=W2+T2
Z2,Z4	Z2	=IF(E2<>"John Doe",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))))),1)
AA2,AA4	AA2	=IF(E2="John Doe",SUM(N2*LOOKUP(N2,{0,5001,7501,10001},{0.25,0.5,0.75,1}),IF(B2="New Logo",500,0),MIN(U2*250,750)),SUM(X2:Y2)*Z2)

I apologize for the delay. Let me give this a try, and I will let you know if it worked.

 

[Dandelion3]

I apologize for the delay. Let me give this a try, and I will let you know if it worked.

You are a lifesaver! It worked. Thank you

 

[dreid1011]

You are a lifesaver! It worked. Thank you

Wonderful! I am glad we reached a working solution. You're welcome.