# Rounddown to calculate qty. Qty dependent on variable units

#### Mndlssbrndlsm

##### New Member
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.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.

Assuming the same cell references as Andrew, try . . .
Code:
``````=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.

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

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

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).

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
Code:
``=rounddown(MIN(A1/(A2*(1+A3)),(A1-A4)/A2),0)``

I still think there's a rounddown requirement though.

I left that as an exercise for the OP.

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

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

Replies
30
Views
2K
Replies
4
Views
470
Replies
11
Views
2K
Replies
4
Views
1K
Replies
1
Views
365

### Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back