Rounding issues regarding stock.

[DazCD]

Good morning,

Hopefully some one can help me with an issue i've come across.

The scenario:

We have a pack of 6 components, to calculate the individual value of 1 item, we simply do the following calculation: 1/6=0.1666666666666667

Since this many decimal places is impractical to use for us, someone rounded this down to 3 decimal places: 0.167

The problem that is now affecting stock is this rounded value, when multiplied by 6, doesn't equal 1. (0.167*6=1.002)

Is a different way of tackling this? I understand this is a math's question, but we work primarily in excel.

thank you for any replies.

 

[HighAndWilder]

Good morning,

Hopefully some one can help me with an issue i've come across.

The scenario:

We have a pack of 6 components, to calculate the individual value of 1 item, we simply do the following calculation: 1/6=0.1666666666666667

Since this many decimal places is impractical to use for us, someone rounded this down to 3 decimal places: 0.167

The problem that is now affecting stock is this rounded value, when multiplied by 6, doesn't equal 1. (0.167*6=1.002)

Is a different way of tackling this? I understand this is a math's question, but we work primarily in excel.

thank you for any replies.

Use 1/6, or whatever the pack quantity is, instead of the rounded decimal.

 

[Joe4]

Yes, that is to be expected. If you elect to remove precision, you will have those small rounding differences. It is just a basic fact of math!

There are a few other options, such as:
1. Do NOT round the calculation, simply change the formatting of the cell to only show 3 decimal places. Then it will show 0.167, but really have 0.166666666... stored. So your calculations will be correct.
- or -
2. Round your second calculation to 2 decimals, so it will show 1.00 instead of 1.002.

 

[DazCD]

It seems so obvious doesn't it really when you look back.

I'll 'like' both but both are very much applicable so I won't pass one off as the solution.

thank you both.

 

[Joe4]

You are welcome.