How to set up an Excel spreadsheet to solve this problem?

Factual Questions
1

I play an MMORPG where there is an auction. The auction has a place where other players can make orders, or a set price to buy a set amount of materials. The price they set is in percent. For example, they want 100 units of X for 110% of it’s value. If the value is .01, they will pay .011 each or 1.10 for all 100 units at the same time. However, the minimum price I can sell my items is in whole units. In the above example, I could sell 100 units for 1.0 or 2.0, nothing else. If I sell for 1.0, they get it and I don’t get the extra 10%. If I set it for 2.0, I can’t sell it to them.

This starts getting really tricky when there are fractional percents (110.2%, for example.) I am perfectly willing to withhold some merchandise to make the order amount, but usually I’m in a guessing situation where I’m still off by a few decmial percents than the full price the buyer is willing to pay. Also, I got screwed one time when the rounding didn’t go my way, and apparently I had placed the item on bid for 110.21% or something.

I think Excel can handle this problem, but I forget how to set up the equation.

Thanks in advance.

2

Forgive my obtuseness, but what exactly is the problem you’re trying to solve? What do you want Excel to do?

3

Your question is hard to understand but I’ll take a guess.


cell B2 --> 110.2%    <-- enter any arbritrary percentage target here

cell B3 --> =100/B2   <-- 90.7441 is the number of units per price of 1.0

…but 90.7441 is not a whole number so you need to use either INT() or CEILING() to round it up or down. You can also use ROUND() if you don’t care that the rounding will fluctuate up or down depending on the rounding rules.


cell D3 --> =INT(B3)   <-- this INT() function returns 90 which is slightly in your favor

cell E3 --> =CEILING(B3,1)    <-- this CEILING() function returns 91 which is slightly in your buyer's favor

If you must sell in exact units of 100 (instead of 90 or 91), then you need extra step of finding the Least Common Multiple between 90-and-100 or 91-and-100. Excel has a builtin function LCM() for this.


cell D4 --> =LCM(D3,100)     <-- 900 units
cell D5 --> =D4/D3           <-- price of 10.0

cell E4 --> =LCM(E3,100)     <-- 9100 units
cell E5 --> =E4/E3           <-- price of 100.0

You can certainly streamline and combine all the calculations above. The steps were broken out to show the thought process.