1
$\begingroup$

I am trying to make a formula where a "table lookup" is not required. Given these two tables:

  Days   Discount          Refs         Rent_Cost   ------------------       ------------------------   15     0.00              0-250        $0.200    30     0.05              251-500      $0.210    60     0.10              501-750      $0.220    90     0.18              751-1000     $0.230    150    0.25              1001-1250    $0.240    240    0.30              1251-1500    $0.250                             1501-1750    $0.260  

Total_Cost(Refs,Days) = Refs * Rent_Cost * (1-Discount) * (Days/30)

Where Rent_Cost and Discount would have to be looked up on the two tables above.

So in order to find a formula for Rent_Cost, I used FindGraph (http://www.uniphiz.com/findgraph.htm) and plotted all 0 through 1750 Refs with what their Rent_Cost was. I got inaccurate results but it looks as if I played around enough I could get to a formula that would work if I rounded by 0.01. I came up with:

Rent_Cost(Refs) = 0.195 + (4E-05 * Refs)

Where Rent_Cost would be rounded to the nearest hundredth. This works 100% with no errors.

Then I did the same with Days and Discount and came up with

Discount(Days) = 0.34 - 0.1 ^ ([Days - 137]/-100)

This works for all Days except for 60. 60 comes out as 0.12 instead of 0.10. You also have to round to the nearest hundredth on this one as well.

If you were to plugin Discount and Rent_Cost into the original formula:

Total_Cost(Refs,Days) = Refs * (0.195 + (4E-05 * Refs)) * (1-(0.34 - 0.1 ^ ([Days - 137]/-100))) * (Days/30)

So I am asking how some of you would go about improving the formula and hopefully get rid of the inaccurately of my Discount function.

Note: I do not know how to use MathJaX. Please edit my post if its hard to read?

  • 0
    @Trevor No I am not. I already do all of this myself in VBA. I am actually going to "release" this formula to the public for other people to use so that have something to go on.2011-01-13

1 Answers 1

3

First, I'd rewrite the rent cost function as Rent_Cost(Refs) = max(0.20, 0.19 + 0.01 * ceiling(Refs / 250)) or $\text{Rent Cost}(\text{Refs})=\max(0.20, 0.19+0.01\cdot\left\lceil\frac{\text{Refs}}{250}\right\rceil)$ (no rounding needed). (In fact, I suspect that your current formula might give the wrong result at the boundary values of 250, 500, 750, ...)

Now, to the discount function. First, note that $\left\lceil\frac{\text{signum}(x-k)+1}{2}\right\rceil$ (where $\text{signum}(x)$ is $0$ if $x=0$, $1$ if $x>0$, and $-1$ if $x<0$) is $0$ if $x and $1$ if $x\ge k$. Now, $\begin{align}\text{Discount}(\text{Days})&=0.05\cdot\left\lceil\frac{\text{signum}(\text{Days}-30)+1}{2}\right\rceil+0.05\cdot\left\lceil\frac{\text{signum}(\text{Days}-60)+1}{2}\right\rceil\\&+0.08\cdot\left\lceil\frac{\text{signum}(\text{Days}-90)+1}{2}\right\rceil+0.07\cdot\left\lceil\frac{\text{signum}(\text{Days}-150)+1}{2}\right\rceil\\&+0.05\cdot\left\lceil\frac{\text{signum}(\text{Days}-240)+1}{2}\right\rceil.\end{align}$ This is also an exact formula, no rounding needed.


edit: Were I doing this in Excel, I'd actually use a bunch of nested IF() statements:

Discount(Days) = IF(Days < 30, 0.00,                     IF(Days < 60, 0.05,                        IF(Days < 90, 0.10,                           IF(Days < 150, 0.18,                              IF(Days < 240, 0.25,                                 0.30))))) 
  • 0
    The if statement is how I do it currently. I just wanted something that was a bit less writing and they could just co$p$y and paste easily. Refs will never be 0, so given that I think I can get rid of the whole max thing and be let with `0.19 + 0.01 * Ceiling(Refs/100)` which is very simple.2011-01-13