Is there a possible mathematical solution for this?

Question

I have what might be considered an odd question. I want to see if I can find a formula/equation to help me with the following.

I'm working in a software package that we are using to calculate fees. The problem is there are many limitations on what the application can do, such as little to no functions, character limits, etc.

I need to calculate a pro-rated fee based off of square footage and a month.

Examples:

250 sq ft in October is 50

3200 sq ft in April is 62.50

The base values come from a table, like so:

        0-499   500-999 1000-1999  2000-2999 3000-3999
10      50.00   60.00   75.00      100.00    125.00 
11      45.83   55.00   68.75      91.67     114.58 
12      41.67   50.00   62.50      83.33     104.17 
1       37.50   45.00   56.25      75.00     93.75  
2       33.33   40.00   50.00      66.67     83.33  
3       29.17   35.00   43.75      58.33     72.92  
4       25.00   30.00   37.50      50.00     62.50  
5       20.83   25.00   31.25      41.67     52.08  
6       16.67   20.00   25.00      33.33     41.67  
7       12.50   15.00   18.75      25.00     31.25  
8       8.33    10.00   12.50      16.67     20.83  
9       4.17    5.00    6.25       8.33      10.42  

I've got a nice nested If statement that works fine, however to use the full table (values go up to 60,000+ sq ft) goes well over the 3k character limit...

Here is a snip-it of that If statement:

If($[60RETAIL]MONTH$ = 1,
If(AND($[60RETAIL]SQFT$ > 0, $[60RETAIL]SQFT$ <= 499), 41.67 - 50, 
(If(AND($[60RETAIL]SQFT$ > 499, $[60RETAIL]SQFT$ <= 999), 50 - 60,
(If(AND($[60RETAIL]SQFT$ > 999, $[60RETAIL]SQFT$ <= 1999), 62.5 - 75,
(If(AND($[60RETAIL]SQFT$ > 1999, $[60RETAIL]SQFT$ <= 2999), 83.33 - 100,
(If(AND($[60RETAIL]SQFT$ > 2999, $[60RETAIL]SQFT$ <= 3999), 104.17 - 125,
(If(AND($[60RETAIL]SQFT$ > 3999, $[60RETAIL]SQFT$ <= 4999), 125 - 150,
(If(AND($[60RETAIL]SQFT$ > 4999, $[60RETAIL]SQFT$ <= 5999), 166.67 - 200,
(If(AND($[60RETAIL]SQFT$ > 5999, $[60RETAIL]SQFT$ <= 6999), 208.33 - 250,
(If(AND($[60RETAIL]SQFT$ > 6999, $[60RETAIL]SQFT$ <= 7999), 250 - 300,
(If(AND($[60RETAIL]SQFT$ > 7999, $[60RETAIL]SQFT$ <= 8999), 291.67 - 350,
(If(AND($[60RETAIL]SQFT$ > 8999, $[60RETAIL]SQFT$ <= 9999), 333.33 - 400,
(If(AND($[60RETAIL]SQFT$ > 9999, $[60RETAIL]SQFT$ <= 10999), 416.67 - 500,
(If(AND($[60RETAIL]SQFT$ > 10999, $[60RETAIL]SQFT$ <= 19999), 500 - 600,
(If(AND($[60RETAIL]SQFT$ > 19999, $[60RETAIL]SQFT$ <= 29999), 583.33 - 700,
(If(AND($[60RETAIL]SQFT$ > 29999, $[60RETAIL]SQFT$ <= 39999), 666.67 - 800,
(If(AND($[60RETAIL]SQFT$ > 39999, $[60RETAIL]SQFT$ <= 49999), 750 - 900,
(If(AND($[60RETAIL]SQFT$ > 49999, $[60RETAIL]SQFT$ <= 59999), 833.33 - 1000,
(If($[60RETAIL]SQFT$ > 59999, 917.67 - 1100,
1))))))))))))))))))))))))))))))))))),

Multiply that by 12 and you can see how large it gets.. I'm hoping for an equation that I can use that will shrink my math down to a smaller solution..

Even possible?

Thanks!

EDIT: I stuck this in the Mathematics section as I was looking for a math solution as the program I'm using has very little programming ability. It looks like it is on hold for being in the wrong section? If someone can think of a better area I'd gladly put it there! My first post :)

Thanks!!

EDIT2: I think the limitations of the software win this one.. even after shortening it a little with some of the great suggestions here, it still won't be short enough to fit in the character limitations.

That being said, I have a plan B involving SQL triggers/Stored Procedures that will work, was just trying to avoid using the back end. However the ideas you've provided will help alot as the formulas will shorten my code on the back end!

Thank you everyone!!

Answer 1

That part of the data seems to be piecewise linear:

The blue line ($f$) und red line ($f_2$) are: $$ f(x) = 50 + (45.83-50)(x-10) \\ f_2(x) = 8.33 + (4.17-8.33)(x-8) $$

If your programming language allows to define functions then you can avoid repetition in your code by moving these statements

If(AND($[60RETAIL]SQFT$ > 499, $[60RETAIL]SQFT$ <= 999), 50 - 60

into a function with the changing data as parameters. Reading the parameters for the $k$-th statement from same array variables $p1[k]$, $p2[k]$ etc within a loop will further shrink your code.

If your data itself turns to be out all piecewise linear, you can replace the data in the array with data generated from the piecewise linear function called for some index parameters.

Answer 2

This seems to match

int A[] = {6000,7500,10000,12500};
int tmp = (sqft<500)? 5000 : A[sqft/1000];
int q = month<10? 10-month : 22-month;
return ((tmp * q +11)/22) * 0.01;

Answer 3

In each column of your table, the items obey a straight-line relationship with row number: The item in row 2 is $\frac{11}{12}$ of the item in row 1, the item in row 3 is $\frac{10}{12}$ of the item in row 1, and so on. In general the item in row $k$ is $\frac{13-k}{12}$ times the item in row $1$.

You can shorten your code by exploiting this relationship -- in order to reproduce the table you need only know the top row. But you need to be careful to map the month to the appropriate row number. (If the table always starts in October, then you could map October to k=1, November to k=2, December to k=3, etc., and then apply your formula to obtain fee[k].)