PDA

View Full Version : Minimizing error to fit numerical data points into categories.



bluesheep
06-30-2014, 09:31 AM
Hi,
I am new to excel VBA and I am not quite familiar enough to write this code in vba. I understand the logic behind what im trying to do, but im really bad at the syntax.
So a little overview. I am working on a part of a program that asks the user for x= Revenue and y= Trade Spend. Now I have 10 categories that each consist of values of Revenue and Trade Spend. Revenue = 1/5*Trade Spend
ex.
1:
x=2,000,000
y=400,000
2:
x=50,000,000
y=10,000,000
3:
x=100,000,000
y=20,000,000
4:
x=500,000,000
y=20,000,000
So on...
My question is how to fit any value of x and y given by the user to one of the mentioned categories. Doing this all while having a preference of the y(Trade Spend) over the x(Revenue) of 4:1[80% preference of y and 20% preference of x]. So I have logic figured that I want to minimize the error between the point and the category to be assigned to the category with the least error. Here is some psuedo-code. Can someone help me translate the code for VBA?


Function categorize: inputs = TS, R, list of categories & output = category that minimizes error
R= Revenue
TS= Trade Spend
For each category in list:
Current_error = error_total(current category, TS,R)
If least_error or least_category are unassigned: set them to current values
If current_error is smaller than least_error: least_error = current_error and least_category = current_category

Return least_category

End function

Function error_total: inputs = category, TS,R & output = value of error squared

Return 1*(category_R – R)^2 + 4*(category_TS – TS)^2

End function