Forum Discussion
How to calculate nearest value without decimals
I want to have a formula which enables me to get an optimal M2 calculation.
Example: a product has the following dimensions: 396mm x 579mm. How many pieces result in .000 decimals? In this case 1,243 pieces = 285.000 M2. How to put this in a formula which results in 1,243 pieces? Your help is greatly appreciated.
Grt Arno, The Netherlands.
AKlompOoops! I was wrong here. One product measures just under 23 m2. Not just over 229 m2. Correct? Now you want to find a factor that multiplies the exact size of the product so that the last three digits of the integer are all zeroes. Correct?
If the 229284 is in A1, try this formula is you are on MS365 or 2021.
=1+(MATCH(0,MOD(INT(A1*SEQUENCE(1000,,1,0.001)),1000),0)-1)/1000
This will return 1.243
6 Replies
AKlomp The two measurements in mm result in just over 229 square meters. What the logic of wanting 285.000 m2? Why not 230.000 m2?
- It's in the "just over" part. 1,000 pieces is 229.284 M2 which is simply too inaccurate. This will lead to differences to what we charge the customer for, and what het receives. When ordering 100,000 pieces, that would result in a loss of 28.4 M2 for the customer. With a extremely expensive product, you wouldn't want that.
AKlompOoops! I was wrong here. One product measures just under 23 m2. Not just over 229 m2. Correct? Now you want to find a factor that multiplies the exact size of the product so that the last three digits of the integer are all zeroes. Correct?
If the 229284 is in A1, try this formula is you are on MS365 or 2021.
=1+(MATCH(0,MOD(INT(A1*SEQUENCE(1000,,1,0.001)),1000),0)-1)/1000
This will return 1.243
- Hi,
To take only the nearest value without decimals use rounddown() function
=ROUNDDOWN(A1,0)
0 --> mean no decimals,- Thank you for your reply, but this is not what I mean. I need the lowest number of pieces which result in the first three digits being 000.
