hi - i'm trying to score customers depending on the volume of material they have purchased.
so, customer A has purchased 50,000 units from us, and customer b has purchased 10,000 units. i have a table which says if you buy between 30,000 and 60,000 then you are customer score 4 and if you have purchased between 0 and 29,999 units you are score 3. i know that i can use "IIf ([units]>30,000 AND [units]<60,0000,4,0), however, the scoring range changes each month (our business has a template in excel in which our commercial team set the limit) therefore i somehow need to pass the range through to the IIF statement as a top and bottom limit. Most of this question is for illustritive purposes so i can explain the concept, we actually have 5 categories not just quantity and each has 5 scoring ranges so it is a bit of a task to do it manually, ideally i would load the template into the database and it would automatically sort the formula,
any help is greatly appreciated!
Pete
so, customer A has purchased 50,000 units from us, and customer b has purchased 10,000 units. i have a table which says if you buy between 30,000 and 60,000 then you are customer score 4 and if you have purchased between 0 and 29,999 units you are score 3. i know that i can use "IIf ([units]>30,000 AND [units]<60,0000,4,0), however, the scoring range changes each month (our business has a template in excel in which our commercial team set the limit) therefore i somehow need to pass the range through to the IIF statement as a top and bottom limit. Most of this question is for illustritive purposes so i can explain the concept, we actually have 5 categories not just quantity and each has 5 scoring ranges so it is a bit of a task to do it manually, ideally i would load the template into the database and it would automatically sort the formula,
any help is greatly appreciated!
Pete