Nearest number that is divisible by another to be close to an amount

[Caly]

Hello I am working on a formula that will calculate a number that is divisble by another number and to use that number multiples by a price that be as close as possible to an amount.

Using the formula like this to get the desired amount however running into trouble where it is not working for high values such 20 Or 144.

The total order amount should be equal to or below $20

Trying to get the closest Desired Amount to multiply that by the price that will get as close as possible to be close to $20 but that amount needs to be divisible to the whole number of the Value. For instance if value is 144 and price is 3.74 then the closest number to use divisible by 144 is 4 where 4 multiples by 3.74 comes to 14.96.



Below is the formula with example



Formula written as this

=if ( Value * Price <= $20), Value,

If ( mod ( Value, Round($20/ Price,0))=0, Round($20/Value,0),

If ( Value/2 * Price <= $20, Value/2)



$20 Ceiling



Value. Price. Desired amount. Total Value

20. $2.22. 5. $11.10

12. $4.08. 4. $16.32

48. $1.16. 16. $18.56

144. $3.74. 4. $14.96

 

[Caly]

Thank you very much and thank you for the details to help understand the logic. This is outstanding.