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

 

[kvsrinivasamurthy]

I have tested. Working ok.

 

[Fluff]

Very odd as I get

Fluff.xlsm
	A	B			E
1	Price	Value
2	3.25	144			0
3	0.8	24			1
4	9.75	72			0
5	4.33	50			0
6	1.24	45			0
7	8.76	5			4
8	0.22	2			10
Lists

Cell Formulas
Range	Formula
E2:E8	E2	=LET(a,INT(CEILING(20,$B2)/$B2),IF(ABS(a*$B2-20)>ABS((a-1)*$B2-20),a-1,a))

 

[kvsrinivasamurthy]

It is working ok.

Sl nO	Price	Value	Amount	Check Value
1	3.25	144	6	19.5
2	0.8	24	25	20
3	9.75	72	2	19.5
4	4.33	50	5	21.65
5	1.24	45	16	19.84
6	8.76	5	2	17.52
7	0.22	2	91	20.02

 

[Fluff]

I had not realised that you had decided to move the columns. I assumed that you were using the OP's data as posted.

 

[Caly]

Thank you all so much. Is there a way to have it go to the value will be as high as possible to $20?

For instance if the price is 0.66 and the Value is 48, using =round(0.66/20,0) comes to 2. Taking the 2 result multiplied by the price 0.66 equals 1.32 for the order value. But I am looking for the highest value which should be 24 which the 24 multiples by the 0.66 price is 15.84

Is there a simple formula to write to take the maximum whole number to get close to the $20?

 

[Caly]

Apologies sorry I wrote it backwards … it is working perfectly now thank you everyone in the group. You are all great.

 

[Fluff]

Glad we could help & thanks for the feedback.