If this user and this, then this. If not this user and this, then that
Hi, so I have a complicated formula for a field I am using for calculating commissions based on whether it's a specific user. I know I can create a commissions structure table to make this easier, however the complication comes from a legacy employee we have. Everyone in who is a superintendent in our company has the same commission structure except one person who has a legacy to a previous structure. That is why there is no need to create the table. Every superintendent from now on and always will have this commission structure except the one guy. I want my formula to base the structure off of whether or not he is the user in a specific field.
Normal Commission Structure:
if GM%<=0.4299 you earn $0
if GM%>0.4299 you earn $100
Legacy User: Mike Andrus
His commission structure is this:
if GM%<=0.4299 you earn $0
if GM%>0.4299 & <0.4499 you earn 1% of the total invoiced dollars
if GM%>0.4499 & <0.4999 you earn 2% of the total invoiced dollars
if GM%>0.4999 you earn 3% of the total invoiced dollars
I have the user field as a text field, and I get no errors from the formula, however, every time I try to use the field when he is the user it returns $100 instead of the correct amount.
This is what I currently have:
If([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4299) & (ToText(ToNumber([GM%]<=0.4499))))),[Total Invoiced]*0.01,
([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4499) & (ToText(ToNumber([GM%]<=0.4999)))))),[Total Invoiced]*0.02,
([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4999)))),[Total Invoiced]*0.03,
([Customer - Superintendent 1 - Name]="Mike Andrus" & (ToText(ToNumber([GM%]>0.4299)))),0,
([Customer - Superintendent 1 - Name]<>"Mike Andrus" & (ToText(ToNumber([GM%]>0.4299)))),100,
([Customer - Superintendent 1 - Name]<>"Mike Andrus" & (ToText(ToNumber([GM%]>0.4299)))),0)
What in my formula is causing this issue? All of the (ToText(ToNumber( functions are there because I was getting errors without them. Is this somehow the issue?
------------------------------
Morgan Hall
------------------------------