Calculated field

[jasons]

This seems so simple but for some reason I can't get the result I'm looking for.

On my form I have a field where the user can enter a percentage (bonus) and based on that percentage a dollar amount is populated in the calculated field (Rate).

This is the scale that I'm basing my code off of.

94-96.99% = $8
97-98.99% = $10
99-100% = $13

Code for calculated field.

=Switch([Bonus Percentage]>.9899,13,[Bonus Percentage]<.99 & [Bonus Percentage]>.9699,10, [Bonus Percentage]<.97,8)

I've using the switch function and I'm able to get values populated that fall in between 94-96.99 and 99-100 but not in between 97-98.99 so I'm guess that the & clause doesn't work or I'm not structuring it right.


Any help is appreciated.

 

[pbaldy]

You want "and" not "&". That said, you can rely on the fact that the function will return the value for the first test that evaluates to true, left to right. Your tests can simply be < .97, < .99, etc.

 

[jasons]

Thanks Paul. So based on what you're saying get rid of the AND and go with

=Switch([Bonus Percentage]<.97,8,[Bonus Percentage]<.99,10, [Bonus Percentage]>.99,13)

 

[pbaldy]

Looks good, other than you haven't accounted for a percentage of exactly .99.

 

[boblarson]

My suggestion? Get rid of the Switch function and create a table with your percentages and the values. Since it is likely the values will change over time, you shouldn't really "hard code" them in anyway.

Then you can use a DLookup or other method to pull the value based on the input.