Take the 2-minute tour ×

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?

share|improve this question
It sounds like you might be intending to implement these formulas in a program—if so, what programming language are you targeting? –  Isaac Jan 13 '11 at 19:54
It will be used in excel for the most part. I am trying to get rid of a bunch of VLOOKUPs. However, I am also trying to get it down to a mathematical formula for the purpose if I can or not. I plan on using floor(x + 1/20) for the rounding. –  BHare Jan 13 '11 at 19:59
You are using VBA? –  PEV Jan 13 '11 at 20:05
@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. –  BHare Jan 13 '11 at 20:08
add comment

1 Answer

up vote 3 down vote accepted

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<k$ 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,
share|improve this answer
The if statement is how I do it currently. I just wanted something that was a bit less writing and they could just copy 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. –  BHare Jan 13 '11 at 20:55
add comment

Your Answer


