Formula Help

[Anonymous]

Based off of this table:

I am trying to build a formula that looks at the amount of managed accounts a person has and places them in the correct tier. The tier will decide their possible dollar amounts:



Then, whether a person gets bronze, silver or gold is based off these targets from their above stats:

So using an example, the end result would something like this for each person:

So a real example using the first table for Timothy Witten would be:

56 managed accounts ->Tier 2

-4.17 NPS -> Below Goal->$0 earned

91.23 Retention ->Retention Bronze-> $210 earned

91.75 Satisfaction -> Satisfaction Silver -> $130 earned

Total Bonus earned -> $340

How can I go about setting this up? Thanks!

[Anonymous]

Have you tried creating a 'conditional column' from the QueryEditor?  If I understand the problem correctly, I think that would work.

[Anonymous]

@Anonymous I have not. I'm not sure if that will work in my situation since all the fields listed don't exist in the same table/dataset. I'm displaying them all through relationships.

[Anonymous]

If the data is included in another table, you could try an Append New and add the Conditional Column to the new table.  This would leave your original tables in tact but give you an additional table to work with.