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?