I have a form with fields named "Sales Plan" and "Sales Actual". The user enters the Sales Plan figures, as well as the actual figures for the past month. I use the following to get the percentage of the difference between the two. This can be a positive, or a negative number. I use the following to get the result in the "Value" field of another text box named "Plan_Calc":
((Sales_Plan - Sales_Actual) / Sales_Plan)
This seems to work fine, and the number returned seems to make sense.I have yet another text box that I need to display a score in, named "Plan_Score", depending on this percentage. I've tried setting up Rules, such as "If Plan_Calc is less than 0, Score = 1" and "If Plan_Calc is greater than 0, Score = 2". This works fine.
The problem is when I try to apply my actual formula, it doesn't seem to work. Here's what I'd like to happen:
If the result is greater than/equal +4%, put a 4 in the "Plan_Score" text box.
If the result is 1% to +4% over Plan, put a 3 in the "Plan_Score" text box.
If the result is -1% to Plan, put a 2 in the "Plan_Score" text box.
If the result is less than -1%, put a 1 in the "Plan_Score" text box.
Anytime I add a third Rule, it either just displays the same number (i.e.; 4) no matter what's entered, or it diplays nothing at all. I've tried every combination of "If greater than this number, but less than another number" I can think of. Does InfoPath limit Rules from doing this kind of calculation?
Any ideas, or am I going about this all wrong??