Upon taking a closer look, my spreadsheet cannot even multiply 2 cells next to each other and give a correct answer. I am sure I have mistakes in my formula also but this is different.
=84.76 * .13 should not equal $10.73
=E50*G50 should equal 11.02
All cells are formatted correctly.
I have not seen this issue before of 2 cells side by side giving an incorrect answer. This seems to be recent.
Oh yeah, I have more then one copy. Tried the same on that. It gave a completely different answer.
So I gather that “kablooey” means “somewhat off”, but still a reasonable number.
Those 13 cents come from dividing gross price by gross amount, and the a tual number is perhaps in reality a bit less, but more than 12.5. When it is formatted to 2 decimals it cannot be displayed exactly but is rounded to nearest cent. The exact number, which is still used for your calculation, is a better representation of the actual cost of material for this batch.
With such small unit prices and large multipliers, it is not unusual to use 3 or 4 decimals for unit price.
To have calculations based on the displayed numbers, I suggest that you apply the ROUND() function. An easier way, which also is more likely to give “off” results, is to enable calculation with numbers as displayed.
These are there formatting codes. Both files have the exact same formatting code but give different answers to the exact problem.
E50 * G50 = H50
[$$-409]#,###.00;-[$$-409]#,###.00 * 0.00" oz. " = [$$-409]#,###.00;-[$$-409]#,###.00
Formatting codes do not change the value in a cell. They just modify the way the value is displayed.
With your format, a displayed value of .13 may come from an actual value anywhere between .125 and .134999999999999, which is a range of +/- 3.7% or so. Your price deviation of 29 cents is around 2.4% of the price, well within the expected deviation range of 3.7%.
The other number may also be subject to rounding error, but the error from that will amount to less than .0012%, which is certainly insignificant unless you make soap for an entire nation.
So, when I asked you to explain the true nature of “kablooey”, I was really asking:
- What is the problem that gets different answers?
- Please give us the numbers you calculate with, not just cell addresses.
- What are the different answers?
- What is the expected answer?
…and I may add …
- Does my explanation of rounding errors above clarify, perhaps even solve, your issue?
I have seen it off as much as $10. That greatly effects the price of a 1 bar of soap for a customer.
There is much to digest here on this page and I am going through everything one at a time so I will remember this and get back to you. Its just that this morning I was really confused because it was giveing me 2 different answers on 2 different speadsheets. As you factor in more prices the price difference gets larger and larger. Please bear with me on answering your question their is much to digest on this page. I haven’t programmed anything in 10 years so keeping up with yall is crazy and I appreciate it.
I am back now. Sorry for the delay. Someone posted a link to be able for me to download the file. I don’t see that comment anymore. I really would like to download the file but I don’t know how.
If someone can message me on that I will do it as soon as I get off work.
One error which makes up for the deviation between your “Testing” and “Inventory” figures in the Testing sheet:
The lookup key used for lye is Lye(NaOH) while the key value (ingredient name) for lye in your inventory is Lye NaOH (Chemical formula separated by space vs. delimited by parentheses). This means that cost of lye is never added.
- In scenario 1 this amounts to $1.08 not included in the sum.
- The rounding errors from ignoring the “fraction of a cent” amount to +46 cents, giving a net “assumed error” of -$0.62, I think.
Here I am mixing actual errors, assumed errors and deliberate rounding, which is confusing (to me at least) and a matter of interpretation. Ignore the “net value” if it does not make sense.
- In scenario 2 the typo amounts to $1.12 missing from the sum.
- The rounding error is upwards for all ingredients in this case except fragrance, which yields 40 cents “overcharge” in your checksum
- How the seven dollar deviation came about I cannot say.
And of course, if OP didn’t use a complex SUM of SUMPRODUCTs of VLOOKUPs, and instead calculated result per line, and then only summed those results, then the problem would be easy to spot and resolve.
Ok, I did what keme suggested. I have made sure everything was formatted and made sure everything was the exact same so vlookup can look for exact matches. I took away all “()” and even tried ROUND() and eventually ran out of ideas.
I know my problem now because I did one simple test and maybe yalll should try it too?
As a small example, In LibreOffice type “14.51” in one cell. Now type “.08” in another. Put the answer in another cell. Did you come up with 1.12 ?
Now open another name brand free spreadsheet. I downloaded another one (not going to mention the name).
Do the exact same thing. Did you get the correct answer of 1.16 ?
I did too!
Even though it is only $.04 off in that one problem imagine how off it will be after scores of problems in a millisecond every time you escape a cell. It is not what Keme suggests. This is not a statistical exam. This is real and it effects people’s wallets. $50 dollars out of $800 is not much but it is mine and I would like to keep it!
Note that ROUND() is part of my suggestion to make the spreadsheet calculation (which is based on calculated values present in cells) match the “by hand” calculation (based on values as displayed in the cell grid, rounded to conform to cell format), but it is not my preferred solution. Sorry about that misconception.
I’d use calculated values with all decimals mostly, and round only at “transaction point” (when money change hands, e.g. for selling prices). This will make calculations better represent actual values, be it cost, size or other magnitudes. However, it will make checksums based on displayed values wrong. You will have to accept that, or use a cell formatting which displays full values and accept an untidy display of unit pricing in your inventory.
Also, as @mikekaganski recently commented (and others have indicated before), dividing your calculation into smaller steps - intermediate calculations - makes it easier to spot mistakes and identify their causes.
I finally found the problem.
It had everything to do with “Precision” and not rounding.
I only wanted the spreadsheet to work with what is shown and not the numbers in the background.
To do that go to Preferences > LibreOffice calc > Calculate and check the box “Precision as shown”.
That fixed everything.
I’ve read others having problems with their spreadsheets not adding correctly. This fixed my problem as soon as I clicked the checkbox.
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.
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).
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.
That being said, provide a (redacted) version of your document with sample data and calculations we can inspect, otherwise there will be no help.
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.
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
You didn’t have enough karma. I upped your question so attaching a file should be possible now.
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.