Skip to main content
Solved

Dynamic IF 'anytext' help

  • May 24, 2019
  • 4 replies
  • 16 views
C

Hi everyone,

I’m struggling to nest an IF formula that would apply a sales commission % to any other agents that make a sale.

Current Formula:

IF({Agent}=“Me”, {Agent}*.24,

IF({Agent}="(Help)", {Agent}*.20,

IF({Agent}=Blank(), 0,"")))

For the Help portion, I currently want it to show that if any Agent in future were to make a sale, then the commission % would be applied to them, regardless of who they are.

Best answer by Justin_Barrett

@JonathanBowen thank you for the reply.

It didn’t quite work for me.

Ideally, I’m looking for a way to calculate the 20% commission for whomever I name as an {AGENT} for future sales - but until then, leaving it blank.

  • If “I’m”={Agent}, then 24% * {Grs.Sale}
  • If “Mgr”={AGENT}, 30%*{GrsSale)
  • If “AnyoneElse”={AGENT}, 20%*{Grs.Sale}
  • If BLANK, 0 commission

I know that an easier way would be to keep a running list of Agents and simply link the {AGENT} sheet to that field but the layout I current have auto-calculates after manually entering AR, AP, and AGENT.


Does this apply the proper logic?

IF(NOT({Agent}),
0,
{Grs.Sale} * SWITCH(
    {Agent},
    "Me", .24,
    "Mgr", .3,
    .2
)

If there’s no agent listed, the value is zero. If there’s an agent, then multiply the {Grs.Sale} value by a percentage that depends on who the agent is. If it’s “Me”, it’s 24%. If it’s “Mgr”, it’s 30%. If it’s anyone else, it’s 20%

    4 replies

    JonathanBowen
    Forum|alt.badge.img+18

    Hi @Cal_King, try

    Agent != “Me”

    i.e not equal to me

    JB

    C
    C
    • Cal_King
    • Author
    • New Participant
    • May 24, 2019

    Hi @Cal_King, try

    Agent != “Me”

    i.e not equal to me

    JB


    @JonathanBowen thank you for the reply.

    It didn’t quite work for me.

    Ideally, I’m looking for a way to calculate the 20% commission for whomever I name as an {AGENT} for future sales - but until then, leaving it blank.

    • If “I’m”={Agent}, then 24% * {Grs.Sale}
    • If “Mgr”={AGENT}, 30%*{GrsSale)
    • If “AnyoneElse”={AGENT}, 20%*{Grs.Sale}
    • If BLANK, 0 commission

    I know that an easier way would be to keep a running list of Agents and simply link the {AGENT} sheet to that field but the layout I current have auto-calculates after manually entering AR, AP, and AGENT.

    Justin_Barrett
    Forum|alt.badge.img+21

    @JonathanBowen thank you for the reply.

    It didn’t quite work for me.

    Ideally, I’m looking for a way to calculate the 20% commission for whomever I name as an {AGENT} for future sales - but until then, leaving it blank.

    • If “I’m”={Agent}, then 24% * {Grs.Sale}
    • If “Mgr”={AGENT}, 30%*{GrsSale)
    • If “AnyoneElse”={AGENT}, 20%*{Grs.Sale}
    • If BLANK, 0 commission

    I know that an easier way would be to keep a running list of Agents and simply link the {AGENT} sheet to that field but the layout I current have auto-calculates after manually entering AR, AP, and AGENT.


    Does this apply the proper logic?

    IF(NOT({Agent}),
0,
{Grs.Sale} * SWITCH(
    {Agent},
    "Me", .24,
    "Mgr", .3,
    .2
)

    If there’s no agent listed, the value is zero. If there’s an agent, then multiply the {Grs.Sale} value by a percentage that depends on who the agent is. If it’s “Me”, it’s 24%. If it’s “Mgr”, it’s 30%. If it’s anyone else, it’s 20%

    C
    C
    • Cal_King
    • Author
    • New Participant
    • May 25, 2019

    Does this apply the proper logic?

    IF(NOT({Agent}),
0,
{Grs.Sale} * SWITCH(
    {Agent},
    "Me", .24,
    "Mgr", .3,
    .2
)

    If there’s no agent listed, the value is zero. If there’s an agent, then multiply the {Grs.Sale} value by a percentage that depends on who the agent is. If it’s “Me”, it’s 24%. If it’s “Mgr”, it’s 30%. If it’s anyone else, it’s 20%


    @Justin_Barrett thank you so much! It worked!!!

    Terms & ConditionsAccessibility statement