Formula help

[pungigi]

Ok, now I am really stuck, here goes. I have the following formula

if {@Avg Cost}<={@Rank Avg Cst}
then 1
else if {@Avg Cost}<=({@Rank Avg Cst}*1.1)
then 2
else if {@Avg Cost}<=({@Rank Avg Cst}*1.2)
then 3
else if {@Avg Cost}<=({@Rank Avg Cst}*1.3)
then 4

Here is the @avg Cost formula
if {@Total Trips}<>0
then ({@Comp $ Paid}+{@NS $ Paid})/{@Total Trips}
else 0

Here is the @ rank Avg cost formula
{@Rank Cost}/{@Rank Count}

What I am wanting is the following:
Average Cost Ranking
VDR ABC 34.56 1
VDR DEF 38.97 2
VDR GHI 45.62 3

STATE AVG 39.71

With my 1st formula above I am trying to rank the individual vendors based on the % they are within the state average. What doesn't seem to work is the @avg cost formula but ONLY in the 1st formula.

 

[dgillz]

Is you Average cost already returning the values you listed? If so there is no need to use a formula to rank them, just use the TopN/Sort expert.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pungigi]

Unfortunately, I can't use the TOPn, reason being is I need to show that a particular vendor is ranked based on his his average trip cost.

EXAMPLE

Average State Trip Cost is 34.26. Ranking levels are...
Average = 1 < 34.26
Within 10% of state cost = 2 > 34.26 < 37.68
Within 20% of state cost = 3 > 37.68 < 41.11
Within 30% of state cost = 4 > 41.12

ABC is 34.26 = 1
CDE is 36.89 = 2
FGH is 42.86 = 4
IJK is 35.87 = 2

There are hundreds of vendors per state and each one has to have it's own ranking.

Hope this helps.