Use Excel like Choose & Match

[crimedog]

I have everything working right so far - Variable reps with variable Quotas etc...


Now I need to take their Quota achievement and evaluate it against ranges to determine the payout.
So
In Excel I would use:

=CHOOSE(MATCH(E4,G4:L4,1),0,0.6,0.75,0.85,1,1.25)

the range G4:L4 have the gates to attain and E4 would be the raw number

I could do it with a nested IIf statement but it would get too involved.

[June7]

Need to know more about data structure.

Might need DLookup() or join tables in query.

Did you mean 'rates' instead of 'gates'?

[ranman256]

Usually all sums,calcs, etc, are done with queries. Study queries.