I am trying to enter the formula to calculate the amount of shares that can be purchased with a specific amount, rounded down to the next whole number. Example: ( Amount available / share price ) rounded down to whole number
I just quite get the hang of the ROUNDOWN function and where to enter it.
Thanks for any help.
MacBook Pro (Retina, 15-inch, Early 2013)
I finally figured this one out. The answer is as follows:
=ROUNDDOWN ((D2/E2), 0)
Where D2 is a currency set to two decimals places and E2 is a number set to 2 decimal places and the total produces a whole number.
I am using this to calculate amount of stock that can be purchased with a certain amount, i.e. E2 = $1993 purchase power, D2=5.40 a share and the final calculation gives a whole number of shares that can be purchased as 368.
Hi Gemma,
Correct formula, but there's no need to set the precision of either D2 or E2. Setting the number of decimal places affects only what is displayed in those cells. It does not change the actual values.
Here are two alternate formulas that achieve the same result: the number of whole shares that can be purchased using a specified amount, and given a set price:
A: =INT(E2/D2)
B: =TRUNC(E2/D2,0) note: the 0 is optional. It sets the place, relative to the decimal point, to which the number is truncated.
C: ROUNDDOWN(E2/D2,0) note: Parentheses around E2/D2 are not necessary. ROUNDOWN does require the zero.
F: =E2/D2 (This returns the full result of the diviion, in this case a repeating decimal)
Cells in row 2 are set to Number, 2 decimal places. Cells in row 3 are set to Automatic. The header row indicates the formula used in each column.
Regards,
Barry
calculating a divisible amount w/ a rounddown
