Forum Discussion

Copper Contributor

Feb 08, 2022

Solved

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,24...

excel

Formulas and Functions

Riny_van_Eekelen
Feb 08, 2022
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

Riny_van_Eekelen

Platinum Contributor

Feb 08, 2022

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?

AKlomp

Copper Contributor

Feb 08, 2022

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.

Riny_van_Eekelen
Platinum Contributor
Feb 08, 2022
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
- AKlomp
  Copper Contributor
  Feb 08, 2022
  Hi Riny, that's more like it! I need to experiment a little to make it more universal for my needs, but I think this is what I'm looking for. Many thanks!