Rounddown to calculate qty. Qty dependent on variable units

[Mndlssbrndlsm]

Hi Everyone

I hope someone can help me with a problem I'm facing.

I have a set amount of cash. Say 1000 (A1).
I want to calculate how many units I can buy with this 1000. The units trade at a price of 1.5 (B1).
However, the issue is that for purchasing these units I will need to pay a variable rate of commission. This commission is 1% of the purchase price, with a minimum of 2.5.

It is the variable rate of commission that is causing me difficulties as I cannot work out how to calculate the number of units I can buy without knowing the commission. And can't work out the commission without knowing the number of units I can buy

Does anyone know a rounddown (or similar) formula i could use to calculate this?

Apologies, this is more of a simple algebra problem then an excel one, but I just can't work this out.

Thanks in advance for any replies.

 

[Andrew Poulsom]

Try:

=MIN(A1/(A2*(1+A3)),(A1-A4)/A2)

where A1 contains the amount available, A2 the unit price, A3 the commission rate and A4 the minimum commission.

 

[Gerald Higgins]

Assuming the same cell references as Andrew, try . . .

=IF(A1/(A2*(1+A3))>=(A4/A2*A3),ROUNDDOWN(A1/(A2*(1+A3)),0),
ROUNDDOWN((A1-A4)/A2,0))

Not as neat as Andrew's (it can probably be simplified), and gives some different results.

For example, when A1 (cash available) is 100, my formula returns 66, Andrew's returns 65. I think mine is correct, but maybe not.

For example, when A1 is 3, my formula returns 1, Andrew's returns 0.333.

 

[jasonb75]

Again using the same cell references as Andrew (results not compared)

=INT(MIN(A1-A4,A1/(1+A3))/A2)

 

[Andrew Poulsom]

Not as neat as Andrew's (it can probably be simplified), and gives some different results.

For example, when A1 (cash available) is 100, my formula returns 66, Andrew's returns 65. I think mine is correct, but maybe not.

For example, when A1 is 3, my formula returns 1, Andrew's returns 0.333.

65*1.5=97.5, leaving 2.5 for commission. You can't by any for 3 because the minimum commission is 2.5, leaving only 0.5 available )1/3 of 1.5).

 

[Gerald Higgins]

Andrew you're right, I would like to edit post #3, delete think and replace with thought

I still think there's a rounddown requirement though.

Maybe

=rounddown(MIN(A1/(A2*(1+A3)),(A1-A4)/A2),0)

 

[Andrew Poulsom]

I still think there's a rounddown requirement though.

I left that as an exercise for the OP.

 

[Mndlssbrndlsm]

Re: Rounddown to calculate qty. Qty dependent on variable units (SOLVED)

You guys are life savers! Thank you all very much.