# Is there a more accurate / simpler formula than this?

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? - 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. – ParoX 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. – ParoX Jan 13 '11 at 20:08 ## 1 Answer 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,
0.30)))))

-
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. – ParoX Jan 13 '11 at 20:55