VLookup is not adding correctly?

[keme]

The question you linked to as equivalent was resolved. The accepted answer was one about rounding error.

**The error there was of this magnitude: **

• Consider a dime. Divide it in a million pieces. Can you imagine how small that is?
• Now divide one of those tiny pieces in a million smaller pieces. Imagine…

The smallest piece there represents the amount of money “lost to rounding error”. It is insignificant.

With your issue, the perceived error is more than a millionth of a millionth of a dime. Still, I agree with you that the reason is the same:rounding error.

As long as you refuse to share the file, and even to give us any numbers, you just keep us guessing. it is not possible to determine the certain cause by guessing, but that this is the most likely cause. If so, the calculated numbers in spreadsheet are correct, and your check (using rounded values as displayed) is in error.

[keme]

I suggest that you take a copy the calculation form when it displays a calculation which looks wrong, and “paste special” (ctrl+shift+V) into a new sheet with only Numbers and Format ticked. Save that “hardcopy” of the calculation. Edit your original question, and upload the file with copied data there. That way, we get the actual data to look at, and you do not risk any intrusion on “intellectual property” represented by the original Lye calculator file.

This way we have a possibility to determine whether the cause is rounding error, data type error, calculation error or something else. It is not certain to reveal the cause, but it sure beats working with a blindfold (which you currently have us doing).

[erAck]

Please do not use the Answer field for comments that are not an answer to the original question, use add a comment instead, or edit your original question to provide further details. Thanks.

[erAck]

That being said, provide a (redacted) version of your document with sample data and calculations we can inspect, otherwise there will be no help.

[iwannaapple]

There is no worries to intellectual property because I am the one that made it. No problem there.
Hmm, I am not really familiar with what keme said. I have no problem sending the entire file with specific scenarios where it goes wrong. I have some orders to fill so I will download it later tonight or first thing in the morning. Thank you guys for helping me just bear with me on the downloaded file please.

[iwannaapple]

I read to download a file I should click on the paperclip icon in the tool text bar. This text bar does’nt show a paperclip. It show B, I, Hyperlink world button, preformatted text button, numbered list button, Bullet list button. No paperclip

[erAck]

You didn’t have enough karma. I upped your question so attaching a file should be possible now.

[keme]

You didn’t have enough karma …

Ah! Sorry, I didn’t think of that. @erAck to the rescue. Thanks!

[keme]

I finally found the problem. It had everything to do with “Precision” and not rounding.

Choosing a level of precision is the same as choosing a point for rounding. Your displayed numbers are rounded. Using “precision as shown” is a choice to carry all rounding errors over from one calculation step to the next.

When you first divide into small units (oz) and set the unit price with two decimals, this introduces a small (less than half a cent, i.e. “invisible”) rounding error. Your recipe will use multiple units of each ingredient. Multiplying will also multiply the error.

If you are satisfied that “precision as shown” solves the issue for you, go ahead and use it. I advise against selling this calculator. Users who operate on larger batches and lower profit margins than indicated in your examples, are likely to lose business/profit from the “deliberate” rounding error. They will look for someone to blame.

I edited my answer, adding a spreadsheet to illustrate what happens.