Rolling Scale [3MonthRank]/([Past_3_Month#]-1)*5"

[TBC]

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

[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]

 

[vbaInet]

I don't know what a rolling scale means so if you would explain what you're trying to achieve we might be able to help.

 

[TBC]

Hi vbaInet,

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)

I have a attached a excel sample to help

Thanks again vbaInet

 

[vbaInet]

If that's the Excel equivalent then we can translate in Access like this:

=5-(([Field] / DMax("[Field]", "TableOrQuery")) * 5)

 

[TBC]

Thanks vbaInet, that worked perfect

 

[vbaInet]

You're welcome!