Calculating bid price from total cost.

Question

Hi, I have to following problem to solve. I have to calculate the bidding price from a total price. For example, a client wants to pay 2000$ as a total price, but from this price, there are added costs:

-usage price which is 10% from the bidding price with a minimum of 10$ and a maximum of 50$

-seller price: 2% from bidding price

-added price: 5$ for a bid between 1 and 500 10$ for a bid between 501 and 1000 15$ for a bid between 1001 and 3000 20$ for a bid over 3000$

-storing cost: 100$

from all this, I have to calculate the bidding price for a total of 2000$, for example. I kind of have no clue of how this can be done. Can anyone give me some hints or pieces of answer of what the algorithm should be?

EDIT: ok I got how to calculate the algebra, now where i'm stuck is how to write the algorithm in code or pseudo-code. Anyone got a hint?

Answer 1

You can express all those costs as a function of the bid price. Generate a giant equation that is the sum of all those functions and solve for a particular final value, e.g.:

usage cost(bid) = PIN(bid*0.10, 10, 50)
seller cost(bid) = bid*.02
added cost(bid) = PIN(ceiling(bid/500)*5, 5, 10) + PIN(ceiling((bid - 1000)/2000)*5, 0, 10)
storing cost(bid) = 100

So the final cost is something like:

final cost(bid) = PIN(bid*.1, 10, 50) + pin(ceiling(bid/500)*5, 5, 20) + PIN(ceiling((bid - 1000)/2000)*10, 0, 20) + bid*.02 + 100 + bid

Solve for a particular value and you're done.

For example, if you want the total cost to be $2000:

2000 = PIN(bid*.1, 10, 50) + pin(ceiling(bid/500)*5, 5, 10) + PIN(ceiling((bid - 1000)/2000)*5, 0, 10) + bid*.02 + 100 + bid.

Bid must be at least > 1500 and < 2000, which works out nicely since we can make those PIN sections constant:

2000 = 50 + 10 + 5 + 100 + bid*1.02
1835 = bid*1.02
bid = 1799.0196078431372549019607843137

The PIN expressions are the hardest to factor out, so you might have to guess a few times until you get something that narrows down the range of bids you want to calculate.

Answer 2

For code, I opened OpenOffice.org Calc, I inserted this table.

    A          B
1  bid        2000
2  usage      =MAX(MIN(0.1*B1;10);50)
3  seller     =0.02*B1
4  added      =IF(B1<500;5;IF(B1<1000;15;IF(B1<3000;15;20)))
5  storage    100
6  total      2205

Then I highlighted cell B6, then clicked on Tools -> Goal Seek.... I choose 2000 for the "Goal value", then put B1 as the "Variable cell". I got the same value as MSN, 1799.01960784314.

Too bad OO-Calc didn't have a PIN() function. Though, it is a weak example of syntactic sugar -- combing MIN/MAX works fine.