Help with formula to be divisible by a whole number

[Standing bear]

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?

 

[bosco_yip]

@Standing bear,

Hi to you, and welcome to the board.

1} I know you are not new to us since you have registered to us in June 2021 for 2 years of time.

2] But I still attached our forum rule to you for read.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

3] Please attached a file with examples data as per your above stated, but please give us the criteria and the expected results as well.

4] A lot of people in here want to help you, and I hope to have your reply in the earliest possible.

Bosco Yip

 

[Standing bear]

Thank you. My apologies. Please see attached example. I am very appreciative of your support and guidance.

 

[Fluff13]

Cross posted https://www.mrexcel.com/board/threads/nearest-whole-number-to-divide-by-to-be-near-a-value.1239423/

 

[bosco_yip]

Thank you. My apologies. Please see attached example. I am very appreciative of your support and guidance.

1] I think to use a ROUND function with digit 0, will round up to the nearest whole number. And will meet with you need.

Because Excel Formula use mathematic round, VBA use banker round.

So,

2[ In C2, formula copied down :

=ROUND(A$12/A2,0)

3] In D2, formula copied down :

=A2*C2

 

[Peter Bartholomew]

Using 365 you could go for the solution as a single dynamic range

= LET(
    roundedCount, MROUND(orderValue/Price, 1),
    HSTACK(roundedCount, roundedCount * Price)
  )

Equivalently

= LET(
    roundedAmt, MROUND(orderValue, +Price),
    HSTACK(roundedAmt/Price, roundedAmt)
  )