Nearest whole number to divide by to be near a value

[Caly]

Hello I have a range of numbers and need to make a formula where that larger number can easily be divisible by a whole number in order to then have the price be near $20. It can be above or below but as close as possible to $20.

I know I’m sure I can use Goal Seek however I need to have it be a formula to be applied to the range of number variations.

Example below
Values the largest number is where these need to be divisible down to a whole number that is lower

144
24
72
50
45
5
2


these each have different prices
Value. Price
144. 3.25
24. 0.80
72. 9.75
50. 4.33
45. 1.24
5. 8.76
2. 0.22

So for instance with the 144 value that has a price of 3.25, what is the nearest whole number the 144 can be broken down to to make the total value of that multiples by the price to be near $20? Like how many units are needed to be divided down using the 144 that would be a whole number to be close either above or slightly below $20 order value?

Price	Value	Divisible number from the Value column	Order Value	Notes/Details	Possible divisible number	Order value	Details
3.25	144	'=B2/xxx	=C2*A2	Need to divide 144 by a number that will be a whole number but that number needs to be the closest value that will be near $20.	=144/24 which comes to 6	=6 *3.25 which comes to 19.50	I did this manually but need a formula to copy down for all cells to do the calculation
0.8	24
9.75	72
4.33	50
1.24	45
8.76	5
0.22	2
Range for order value
$20

Also asked here Help with formula to be divisible by a whole number

 

[JamesCanale]

Here is something. It's not good with small numbers or prime numbers. It's 365.

MrExcelPlayground18.xlsx
	A	B	C	D
1	20
2	Price	Value	Amount	Check
3	3.25	144	6	19.5
4	0.8	24	24	19.2
5	9.75	72	2	19.5
6	4.33	50	5	21.65
7	1.24	45	15	18.6
8	8.76	5	5	43.8
9	0.22	2	2	0.44
Sheet4

Cell Formulas
Range	Formula
C3:C9	C3	=LET(x,B3,y,A3,t,$A$1,a,SEQUENCE(x/2+1),b,x/a,c,FILTER(b,INT(b)=b),d,ABS(y*c-t),e,MIN(d),FILTER(c,d=e))
D3:D9	D3	=A3*C3

 

[Fluff]

Another option for 365

Fluff.xlsm
	A	B	C	D
1	Price	Value	Divisible number from the Value column	Order Value
2	3.25	144	6	19.5
3	0.8	24	25	20
4	9.75	72	2	19.5
5	4.33	50	5	21.65
6	1.24	45	16	19.84
7	8.76	5	2	17.52
8	0.22	2	91	20.02
9
10
11	Range for order value
12	20
Lists

Cell Formulas
Range	Formula
C2:D8	C2	=LET(s,B2/(B2*A2/$A$12),x,INT(s),y,CEILING(s,1),xa,ABS(A2*x-$A$12),ya,ABS(A2*y-$A$12),IF(xa=MIN(xa,ya),HSTACK(x,x*A2),HSTACK(y,y*A2)))
Dynamic array formulas.

 

[Fluff]

I was definitely over-thinking it. A far simplar solution has been posted on Chandoo by @bosco_yip

=ROUND(A$12/A2,0)

 

[JamesCanale]

But that always going to be a whole divisor of the B column?

 

[Fluff]

It gives the same results as I get, but then it depends on how you read the question as to whether it's right or not.

 

[kvsrinivasamurthy]

Col A=Sl No, Col B=Price, Col C = Value, Col D ="Amount Col E= Check value
In D2

=IF(ABS(INT(CEILING(20,$B2)/$B2)*$B2-20)>ABS(INT((CEILING(20,$B2)/$B2)-1)*$B2-20),INT((CEILING(20,$B2)/$B2)-1),INT((CEILING(20,$B2)/$B2)))

In E2
=B2*D2

 

[Fluff]

Have you actually tested that formula?

 

[kvsrinivasamurthy]

Col A=Sl No, Col B=Price, Col C = Value, Col D ="Amount Col E= Check value
For 365 version
In D2

=LET(a,INT(CEILING(20,$B2)/$B2),IF(ABS(a*$B2-20)>ABS((a-1)*$B2-20),a-1,a))

In E2
=B2*D2

 

[Fluff]

That still doesn't work. Why not test your ideas before posting.