I'm trying to get I think it’s called a rolling scale.

[Dave Smith]

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

3Month_0to5Rank: [3MonthRank]/([TotalRecords]-1)*5

[SQL]
SELECT [CUSB_Rank tbl_1].[OMNI#], [CUSB_Rank tbl_1].[TotaRecords$], [CUSB_Rank tbl_1].[TotaRecords#], [CUSB_Rank tbl_1].Jan_Loan_Amount, [CUSB_Rank tbl_1].Jan_App_Count, [CUSB_Rank tbl_1].Feb_Loan_Amount, [CUSB_Rank tbl_1].Feb_App_Count, [CUSB_Rank tbl_1].Mar_Loan_Amount, [CUSB_Rank tbl_1].Mar_App_Count, [CUSB_Rank tbl_1].Apr_Loan_Amount, [CUSB_Rank tbl_1].Apr_App_Count, [CUSB_Rank tbl_1].May_Loan_Amount, [CUSB_Rank tbl_1].May_App_Count, [CUSB_Rank tbl_1].Jun_Loan_Amount, [CUSB_Rank tbl_1].Jun_App_Count, [CUSB_Rank tbl_1].Jul_Loan_Amount, [CUSB_Rank tbl_1].Jul_App_Count, [CUSB_Rank tbl_1].Aug_Loan_Amount, [CUSB_Rank tbl_1].Aug_App_Count, [CUSB_Rank tbl_1].Sept_Loan_Amount, [CUSB_Rank tbl_1].Sept_App_Count, [CUSB_Rank tbl_1].Oct_Loan_Amount, [CUSB_Rank tbl_1].Oct_App_Count, [CUSB_Rank tbl_1].Nov_Loan_Amount, [CUSB_Rank tbl_1].Nov_App_Count, [CUSB_Rank tbl_1].Dec_Loan_Amount, [CUSB_Rank tbl_1].Dec_App_Count, [CUSB_Rank tbl_1].First_3_Month_Loan_Amount, [CUSB_Rank tbl_1].First_3_Month_App_Count, [CUSB_Rank tbl_1].First_6_Month_Loan_Amount, [CUSB_Rank tbl_1].First_6_Month_App_Count, [CUSB_Rank tbl_1].First_9_Month_Loan_Amount, [CUSB_Rank tbl_1].[9Month_App_Count], [CUSB_Rank tbl_1].[12_Month_Loan_Amount_10], [CUSB_Rank tbl_1].[12Month_App_Count_10], [CUSB_Rank tbl_1].First_12_Month_App_Count, [CUSB_Rank tbl_1].[Past_3_Month$], [CUSB_Rank tbl_1].[Past_3_Month#], [CUSB_Rank tbl_1].[Past_6_Month$], [CUSB_Rank tbl_1].[Past_6_Month#], [CUSB_Rank tbl_1].[Past_9_Month$], [CUSB_Rank tbl_1].[Past_9_Month#],

[CUSB_Rank tbl_1].[Past_12_Months$], (Select count(*) from [CUSB_Rank tbl_1] as B where [CUSB_Rank tbl_1].[Past_3_Month#] < B.[Past_3_Month#]+1) AS 3MonthRank, [3MonthRank]/([Past_3_Month#]-1)*5 AS 3Month_0to5Rank INTO CUSB_Master_Table FROM [CUSB_Rank tbl_1];[/sql]

Thanks again
Corey

[Rabbit]

This is the seventh thread you've created regarding ranking records. What is it you're trying to accomplish and why do you keep creating threads and abandoning then?

[Dave Smith]

I wasn’t quit sure what it is called but I have a list of reps that I'm using a Rank formula that ranks my reps buy the number of apps they get.

So now that I have their rank, I would like to put them in a scale from 1-5 with one being the best.

In excel the formula looks like this

=5-(AL3/MAX($AL$3:$AL$9913)*5)

Thanks Rabit