Ask Your Question
1

VLookup is not adding correctly?

asked 2020-05-03 20:58:39 +0200

iwannaapple gravatar image

updated 2020-05-09 10:49:15 +0200

I have a question that is bugging me. I do not understand all the commas, dots and dashes, who what where or how as much as many of you so if you can answer my question please answer without all the computer lingo so I know what your talking about. But here is my question and a brief summary of my worksheet... This is for a lye calculator. Sheet one is an inventory sheet which breaks down all the oils and fragrances by the ounce and when used individually for each loaf of soap it tells correct materials cost. However,sheet 2 is a lye calculator with list boxes for several oils and fragrances. It gets that information from the inventory sheet. What ever I change on the inventory sheet is reflected in list box. So, while I can change each individual oil by using the list box it will change every thing else correctly. Each oil requires a different amount of lye but at the same time each oil costs differently and the menu is set up to see the changing price as the oils and fragrances are changed. My problem: Vlookup is telling me the correct price for each individual oil UNTIL (I think?) it hits an empty cell. For instance: Rows A1:A4 have an oil in each cell. It adds those up correctly. A5 will be blank (or deleted) but when I go to add an oil to A5 the price goes kablooey and it doesn't add correctly anymore. To see what I am talking about you can view this link. https://www.youtube.com/watch?v=PKlAo... I do not make any money on ads or views so I am not trying to sell anything but it continues to bug me why this is not quite correct. Here is my complete formula in the cell that adds the money.....

=IF(ISBLANK(A6),A6="0",((VLOOKUP(A6,MyOils,5,0))*D6))+IF(ISBLANK(A7),A7="0",((VLOOKUP(A7,MyOils,5,0))*D7))+IF(ISBLANK(A8),A8="0",((VLOOKUP(A8,MyOils,5,0))*D8))+IF(ISBLANK(A9),A9="0",((VLOOKUP(A9,MyOils,5,0))*D9))+IF(ISBLANK(A10),A10="0",((VLOOKUP(A10,MyOils,5,0))*D10))+IF(ISBLANK(A11),A11="0",((VLOOKUP(A11,MyOils,5,0))*D11))+IF(ISBLANK(A12),A12="0",((VLOOKUP(A12,MyOils,5,0))*D12))+IF(ISBLANK(A13),A13="0",((VLOOKUP(A13,MyOils,5,0))*D13))+IF(ISBLANK(A14),A14="0",((VLOOKUP(A14,MyOils,5,0))*D14))+IF(ISBLANK(A15),A15="0",((VLOOKUP(A15,MyOils,5,0))*D15))+IF(ISBLANK(A16),A16="0",((VLOOKUP(A16,MyOils,5))*D16))+IF(ISBLANK(A18),A18="0",((VLOOKUP(A18,MyFragrances,4,0))*D18))+SUM(taskCost)+SUM(Packaging)

What this means is If(oil#1 == empty) then {oil#1 == 0} Else { Match oil#1 in MyOils and tell me the pricePerOz; pricePerOz X theQuantityUsedInD6} or at least that is what I think it is doing.

Then add this amount to the next oil in the list .

Your help ... (more)

edit retag flag offensive close merge delete

Comments

Hi,

it is very difficult to answer such a question without having the sheet. I hope my repair works. Please try this: =SUM(IFERROR(VLOOKUP(A6,MyOils,5,0),0)*D6,IFERROR(VLOOKUP(A7,MyOils,5,0),0)*D7,IFERROR(VLOOKUP(A8,MyOils,5,0),0)*D8,IFERROR(VLOOKUP(A9,MyOils,5,0),0)*D9,IFERROR(VLOOKUP(A10,MyOils,5,0),0)*D10,IFERROR(VLOOKUP(A11,MyOils,5,0),0)*D11,IFERROR(VLOOKUP(A12,MyOils,5,0),0)*D12,IFERROR(VLOOKUP(A13,MyOils,5,0),0)*D13,IFERROR(VLOOKUP(A14,MyOils,5,0),0)*D14,IFERROR(VLOOKUP(A15,MyOils,5,0),0)*D15,IFERROR(VLOOKUP(A16,MyOils,5,0),0)*D16,IFERROR(VLOOKUP(A17,MyOils,5,0),0)*D17,taskCost,Packaging)

dscheikey gravatar imagedscheikey ( 2020-05-03 22:03:28 +0200 )edit

No, your attempt is worse because there's a character limit in a comment and the formula expression is even different from what you wrote before. You may delete the comment. I edited your question to format the code as code.

Please attach a sample document with (redacted) data. Our crystal ball is broken. Edit your question to attach a document.

erAck gravatar imageerAck ( 2020-05-03 22:53:49 +0200 )edit

Or very easy: =Sum(SUMPRODUCT(IFERROR(VLOOKUP(A6:A17,MyOils,5,0),0),D6:D17),taskCost,Packaging)

dscheikey gravatar imagedscheikey ( 2020-05-03 23:30:01 +0200 )edit

That works out very nicely with no problems. It puzzles me why there isn't a multiplier by anything but yet it works versus my version VLOOKUP(A10,MyOils,5,0))*D10 Yours is also somehow doing the same thing and so far I am not getting any rounding errors or anything!

How would I also include the following in your formula? VLOOKUP(A18,MyFragrances,4,0))*D18

iwannaapple gravatar imageiwannaapple ( 2020-05-05 02:59:14 +0200 )edit

Still blindfolded, or perhaps I am really going blind to this subject. Sorry!

I see the attachment, but find no explanation of the difference between expected and calculated figures.

Did you upload a file containing deviant results, or do you expect us to experiment with this until we find a deviation?

Most of us do not make soap, and we do not have an intimate knowledge of the calculation model you have built. We also do not know for sure how you determined the expected result and which calculated result you compared it to. It will take significant time (possibly more time than what you have spent in creating it) studying your work to get a full understanding of it.

Please: upload a completed calculation (or three) which looks wrong, and tell us what is wrong with it. (What did you expect?) When we know where to start and what ...(more)

keme gravatar imagekeme ( 2020-05-07 12:59:27 +0200 )edit

Ok, I deleted the old link and uploaded a new one with a completed chart to see the actual results. The chart is calculated 3 ways. By calculator, By using the menu on the Testing tab and by using the Inventory tab. Only the money portions are concerning. everything else works well. I also explained a little more how everything works on another comment I made a few minutes ago.

iwannaapple gravatar imageiwannaapple ( 2020-05-08 02:06:44 +0200 )edit

Is there something simple for examination, like "In cell E5 on sheet X I expect to see 5, but it shows 6"?

Mike Kaganski gravatar imageMike Kaganski ( 2020-05-08 08:08:32 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2020-05-06 02:37:03 +0200

iwannaapple gravatar image

updated 2020-05-21 19:19:13 +0200

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.

edit flag offensive delete link more

Comments

1

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 ...(more)

keme gravatar imagekeme ( 2020-05-06 07:31:30 +0200 )edit

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).

keme gravatar imagekeme ( 2020-05-06 07:46:22 +0200 )edit

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 gravatar imageerAck ( 2020-05-06 19:28:14 +0200 )edit

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

erAck gravatar imageerAck ( 2020-05-06 19:31:13 +0200 )edit

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 gravatar imageiwannaapple ( 2020-05-06 22:41:36 +0200 )edit

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

iwannaapple gravatar imageiwannaapple ( 2020-05-07 11:00:42 +0200 )edit

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

erAck gravatar imageerAck ( 2020-05-07 18:09:12 +0200 )edit

You didn't have enough karma ...

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

keme gravatar imagekeme ( 2020-05-07 21:24:14 +0200 )edit

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 ...(more)

keme gravatar imagekeme ( 2020-05-22 06:53:38 +0200 )edit
0

answered 2020-05-04 01:22:46 +0200

keme gravatar image

updated 2020-05-22 10:30:01 +0200

Analysis

To me the formula seems to work as it should. Had to create the MyOils, MyFragrances, TaskCost and Packaging ranges and changed separator to semicolon (because my locale uses comma for decimal separator). It then extracts prices for every row and sums nicely.

Finding the cause

The cause of error may be a mistake in entry of prices in the MyOils/MyFragrances data ranges. I notice that you sometimes quote a number when it looks like it represents a magnitude. Numbers to use in calculations should not be within quotes.

If you use fractions in cells not "primed" for that with a fraction format, the fraction may be interpreted as a date (which is internally represented by a significantly larger number).

Do your percentages add up to 100?

Those suggestions are just shooting in the dark.

We don't like to be blindfolded when searching for errors, but I understand that this is a commercial product which you don't want to post to the general public. If you don't want to attach your file, it may be easier for us to suggest a cause if you can at least say something about the nature of "kablooey". Do you get too high sum (by how much)? Is it an error message? What? Does it always happen in the 5th row? Does it only happen if you paste figures, not when you type them in?

A better approach

Put the VLOOKUP() parts of your sum formula in a new column (say G, pushing recipe 2 to start in column H), with each single VLOOKUP() in the row where it belongs, then use =SUMPRODUCT(A6:A18;G6:G18) to total the cost of ingredients.Then you can see better how the figures build up to the final sum.

If you are the one selling this calculator on Etsy and you have determined that it doesn't work right, you should notify your customers immediately.

If you bought it and can't make it work right, did you try to contact the seller?

Edit: What happens, why, and how to deal with it

Click here to download a sample spreadsheet illustrating the effect of rounding everything, whether explicit in your formula or by using "Precision as shown". This sheet is grossly simplified compared to what you have, in order to illustrate by having the important steps visible all at once. With each calculation step, there is new potential for accumulating error.

In the Use amount column I just inserted a formula to "use everything you got", so you can compare acquisition cost and calculated cost directly. Probably a silly soap recipe (as previously stated, I know next-to-nothing about soap making), but it illustrates what happens to the bottom line. Change numbers to your real situation to see what actually happens for any given batch you make.

Another point which comes forth is that the cheapest ingredient (when it is the one you use the most of) may yield the most significant ... (more)

edit flag offensive delete link more

Comments

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.

iwannaapple gravatar imageiwannaapple ( 2020-05-04 10:51:06 +0200 )edit

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.

keme gravatar imagekeme ( 2020-05-04 11:19:51 +0200 )edit

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

iwannaapple gravatar imageiwannaapple ( 2020-05-04 11:20:22 +0200 )edit

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 ...(more)

keme gravatar imagekeme ( 2020-05-04 11:51:24 +0200 )edit

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.

iwannaapple gravatar imageiwannaapple ( 2020-05-05 03:08:42 +0200 )edit

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.

iwannaapple gravatar imageiwannaapple ( 2020-05-07 10:53:46 +0200 )edit

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 ...
(more)
keme gravatar imagekeme ( 2020-05-08 11:19:45 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-05-08 15:32:30 +0200 )edit

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 ...(more)

iwannaapple gravatar imageiwannaapple ( 2020-05-09 10:46:34 +0200 )edit

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 @Mike Kaganski recently commented (and others have indicated before), dividing your calculation into smaller steps - intermediate calculations - makes it easier ...(more)

keme gravatar imagekeme ( 2020-05-09 12:03:01 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-05-03 20:58:39 +0200

Seen: 178 times

Last updated: May 22 '20