Results 1 to 2 of 2
2011-01-13, 21:15 #1
- Join Date
- Dec 2010
- Thanked 0 Times in 0 Posts
I'm trying to get I think itís called a rolling scale.
I have 500 records but I would like the rolling scale to rate them from 1-5 with 1 being the best.
The formula I thought would work was "3Month_0to5Rank: [3MonthRank]/([Past_3_Month#]-1)*5" but turns out if keeps going after 5 or I tried switching the formula it gives me more weird numbers.
I really appreciate you taking the time for assist me with this problem.
The formula below worked for a different query but now it is giving me from 0.00125 to 1253.21500
2011-01-20, 04:48 #2
- Join Date
- Dec 2009
- Manchester, United Kingdom
- Thanked 17 Times in 16 Posts
Without opening your ZIP file I'd say the formula needs to be something on the lines of
3Month_0to5Rank: int(round([3MonthRank]/DMAX("[3MonthRank]","[YourTable]") * 5,0))
If not, and the query is too slow, create another query that just returns maxrank:MAX([3MonthRank]) and outer join that to your table, using that field instead of the DMAX function. (Outer join - just add the query to the query grid, no join lines)