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

 

[KRice]

Here is one approach for Excel 365:

Book9
	A	B	C	D
1			Constraint	$20.00
2
3	Value	Price	Amount	Total Value
4	20	$2.22	5	$11.10
5	12	$4.08	4	$16.32
6	48	$1.16	16	$18.56
7	144	$3.74	4	$14.96
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=LET(dvsrs,IFERROR(TRANSPOSE(SMALL(IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),SEQUENCE(2*SQRT(A4)))),""),MAX(FILTER(dvsrs,(dvsrs<>"")*(dvsrs<=INT($D$1/B4)))))

I'm not entirely happy with my approximation for the upper bound on the number of potential divisors a number in A4 may have (estimated here as 2*SQRT(A4)), but I suspect it will work okay while providing a reasonable limit on the size of the arrays formed.

 

[Caly]

Thank you very much. I’m piecing the formula together but what is the “let” and “dvsrs” function? Trying to write it and explain the factors applied

 

[KRice]

LET is a function in Excel 365 that allows us to define a variable and then reuse it multiple times without having to repeat the formula associated with it. In this case, dvsrs is the name I assigned to the formula that delivers an array of divisors for the number appearing in the Value column. You’ll see then that this divisors array is used a couple of times in the subsequent calculation performed inside the LET function.

 

[Caly]

Thank you that is very cool and easy. Is this the divisors array section being referenced for the let function:

iferror(TRANSPOSE(SMALL(IF(MOD(A4,SEQUENCE(A4))=0,

 

[KRice]

Yes, when I was developing the formula, I left that formula stand alone to confirm its behavior, and I wanted the results to spill out to the right on the same row...which is why I used the TRANSPOSE function. However, with the formula consolidated into the LET function, there is no purpose served by TRANSPOSE and it can now be removed without any loss of functionality.

So the divisors array (dvsrs) is/was formed by: IFERROR(SMALL(IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),SEQUENCE(2*SQRT(A4))),"")
Breaking this down further, for a value of 20 in A4, SEQUENCE(A4) forms an array of {1;2;3;...;20}...each element residing on a separate row in the array. We then take MOD(A4,SEQUENCE(A4))=0 to learn which of these numbers can be divided evenly (leaving no remainder) into A4. This logical check is part of the IF statement:
IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4))
When this expression is TRUE, we keep that particular element of the SEQUENCE array (that's what SEQUENCE(A4) does at the end of the expression); and where the expression evaluates to FALSE, the resultant array element will be FALSE. So again, for 20 in A4, this IF expression will produce: {1;2;FALSE;4;5;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20}
And now that I look at this again, I see a way to shorten the formula further by operating directly on this array without forming another intermediate array whose errors were handled with the IFERROR function. A better version is:
=LET(dvsrs,IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),MAX(FILTER(dvsrs,(dvsrs<>FALSE)*(dvsrs<=INT($D$1/B4)))))
This redefines the dvsrs formula and then takes the array of divisors and FALSE values delivered by dvsrs and eliminates the FALSE values and any divisors that are greater than INT($D$1/B4)---this is done in the FILTER function---and then the MAX of the remaining divisors is taken as the answer. So for this example, INT($D$1/B4) = INT($20.00/$2.22) = 9...we don't want to consider any divisors larger than 9...so we are left with a divisor array of {1;2;4;5}, and the MAX is 5.

Book9
	A	B	C	D
1			Constraint	$20.00
2
3	Value	Price	Amount	Total Value
4	20	$2.22	5	$11.10
5	12	$4.08	4	$16.32
6	48	$1.16	16	$18.56
7	144	$3.74	4	$14.96
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=LET(dvsrs,IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),MAX(FILTER(dvsrs,(dvsrs<>FALSE)*(dvsrs<=INT($D$1/B4)))))

 

[Caly]

Thank you wow this is fantastic. Just so I’m understanding is the dvsrs array the first section:

IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4))

And where it says dvsrs that is as if I was writing that array? So like if I didn’t use the let function it would be written as this:

=IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),MAX(FILTER(IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),(IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4))<>FALSE)*(IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4))<=INT($D$1/B4)))))

 

[KRice]

Almost...yes to your first question...dvsrs is defined by the IF function you've shown. But if you eliminate the LET function, then you wouldn't initially lead with the IF formula associated with dvsrs. Instead, that entire IF formula would be pasted into the MAX function everywhere "dvsrs" appears, as shown in the sample below...and this helps to illustrate how the LET function can be used to eliminate redundancy and reduce the chance of typing errors:

Book9
	A	B	C	D
1			Constraint	$20.00
2
3	Value	Price	Amount	Total Value
4	20	$2.22	5	$11.10
5	12	$4.08	4	$16.32
6	48	$1.16	16	$18.56
7	144	$3.74	4	$14.96
8
9	Value	Price	Amount	Total Value
10	20	$2.22	5	$11.10
11	12	$4.08	4	$16.32
12	48	$1.16	16	$18.56
13	144	$3.74	4	$14.96
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=LET(dvsrs,IF(MOD(A4,SEQUENCE(A4))=0,SEQUENCE(A4)),MAX(FILTER(dvsrs,(dvsrs<>FALSE)*(dvsrs<=INT($D$1/B4)))))
C10:C13	C10	=MAX(FILTER(IF(MOD(A10,SEQUENCE(A10))=0,SEQUENCE(A10)),(IF(MOD(A10,SEQUENCE(A10))=0,SEQUENCE(A10))<>FALSE)*(IF(MOD(A10,SEQUENCE(A10))=0,SEQUENCE(A10))<=INT($D$1/B10))))

 

[Caly]

Thank you this is incredible and worked perfectly. Cannot thank you enough. You rock.

 

[KRice]

I’m glad it’s working, and happy to help. I see one other refinement to shorten the formula further: the last term
(dvsrs<=INT($D$1/B4))
can eliminate the integer function so that it becomes
(dvsrs<=$D$1/B4)
…since dvsrs contains either integers or FALSEs and we want only those divisors that are “less than or equal to”, so it doesn’t really matter if we leave the decimal component on $D$1/B4.