VLookup is not adding correctly?

English
,
#1

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.

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 with this would be greatly appreciated. My wife is actually mad at me because it may overcharge customers if I am not careful.
And most importantly. How to shorten this? Is there away to put cells A6:A16 into an array and then use the array to look up the info? Something has to be better then what I am doing?

Too many places to hide a paperclip! I found it. Here is the file…
The testing tab is the one to test with to show how prices will differ greatly and not a penny or two from rounding.

Ok, This is another version with a completed chart of calculations on the “Testing” tab. These calculations are were calculated by using the recipe menu(Cell Testing.F20), by calculating one ingredient at a time using the inventory tab, and by using an actual calculator. The results are shown
But, an explanation of everything is needed first.
The only things that I need help with is the MONEY portions and these are calculated on the “Testing” tab and “Inventory” tabs. The Inventory tab will calculate an entire recipe one ingredient at a time and the Testing tab uses the information from the Inventory tab to calculate ingredients more quickly.
On the testing tab Cells A6:A17 are list boxes to choose any ingredient and cell A18 is another list box to choose any fragrance for more experimenting if you choose.
I suspect it is a rounding problem BUT by the time everything calculates the difference can affect someone’s wallet. I did notice that the difference between the Inventory tab calculations and the Testing menu were not that huge (Although I have seen it differ by as much as almost $10.00)
There are 7 tabs. Only the “Testing” & “Inventory” tabs are important right now.
“Link is deleted now”

[erAck: edited to display code as code (by indenting by 4 spaces) and not attempting to format funny with asterisks]

Can vlookup return an array? easy way to find one value depending on multiple other entries
#2

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)

#3

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.

#4

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

#5

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

#6

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 to look for, we don’t have to look in all the insignificant spots (insignificant for the issue at hand, that is). Help us so we can help you!

#7

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.

#8

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

#9

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

The Using prec. as shown column simulates what happens when you enable said function, so when Precision as shown is enabled, the Calculated Variable cost should equal that column, and the Accumulated rounding error is eliminated (not because the error disappears, but because “precision as shown” defeats my error detection calculation). This also corresponds to what you get when you check your figures “by hand” (using only displayed precision). Disable Precision as shown, and calculated variable cost will reflect acquisition cost for ingredients used.

The sensible choice (from the accountant’s perspective) is for the calculated cost to reflect actual acquisition cost.

#10

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.

#11

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.

#12

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

#13

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?
#14

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.

#15

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.

#16

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.
#17

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.

#18

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!

#19

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.

#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.