I have a puzzling question... puzzling for me that is... I'm hoping one of you geniuses out there can help...

I need to know how to accomplish this set of calculations. I have tried using the Case function, but it doesn't like what I'm typing...

I need to calculate a % on total sales on a sliding scale... Here's everything I'm needing to accomplish and wanted it in one field... but perhaps I need to create multiple fields to accomplish this? Thanks in advance!

______________________________

If TotalSalesYTD are under $2000 I need to multiply TotalSalesYTD by 2%.

If TotalSalesYTD are over $2000 but under $4000, I need to calculate the amount of TotalSalesYTD over $2000 but under $4000 TotalSalesYTD and multiply that difference by a rate of 1%.

If TotalSalesYTD are over $4000 I need to calculate the amount over 4000 and then the rate would be at 0%

_____________________________

You can accomplish this in a number of different ways, but I would not personally look to minimize the number of fields in the solution. You can accomplish all of the calculations in a single field, using Case(). I would suggest three additional fields, however, largely because you are using values that may change over time (comp plans change). One would be a low threshold (2000 in this case) and the other a high threshold (4000 here). The other three would be the percentages you list. This will allow you to change the formula by changing fields to which only you have access, without having to change the calculation field itself. But that's just me.

What is unclear in your question is this: are you doing this as a cumulative tier ($3,000 would return 50), or within only the highest single tier (where $3,000 would return 10)? I suspect the former, but the calculation looks pretty different between these two approaches.