Forum Discussion
What is best formula for this tax rate calculation?
A quick PostScript: You had written: So, since carbonation of all the beverages are below the highest level, the formula always incorrectly returns the result corresponding to the highest tax rate.
And I just wanted to gently suggest that whenever you find yourself thinking "Excel is returning an incorrect result" it would be better to think "What am I doing that is causing Excel to return an unexpected result?" It is 99.99999% more likely that Excel is doing exactly what it's supposed to do, that any "incorrect" result is coming from an incorrect usage. Every once in a while (that 0.00001% of the time) you might indeed have uncovered a bug that has eluded the thousands of Excel users who preceded you..... or maybe that is a well known limitation: if you poke around here long enough you will discover that in really large numbers, or really really small ones, there are some "limits to Excel." In fact, go Google the phrase "limits to Excel" and you'll find it interesting.
VLOOKUP, though, has historically been one of the most popular functions beyond the most basic, and it has been tested far more rigorously than anything you or I could throw at it. It does deliver the correct results. If you're not getting "the right answer" it's because you're asking it the wrong question.
In this case, I truly suspect you were giving it whole numbers, when the first column of the table was in percentages (AKA fractions, aka values less than 1).
mathetes I do not doubt the accuracy of VLOOKUP or really any of the other Excel functions (although I have seen a couple of outcomes that did not conform to the result predicted by Excel itself over the years). Although I am still seeing incongruent outcomes in my worksheet, I can work around it OK I think. I know that VLOOKUP will match the last value that is less than or equal to the lookup value, it's just that VLOOKUP calculation of tax class ranges - or some other aspect - is not returning the results I would have expected if entered manually. Thanks for all your insights!
Can you post the spreadsheet that isn't producing the results you want? Did the one I gave you not do what you wanted. I know it was getting the right tax when I created it, based on the table you provided.
I'm just curious what isn't happening....
Here, I've attached a revised version of what I sent you before. In this one I've added a column that does nothing but look up that tax rate per gallon. (Column I)
Column J then just multiplies that tax rate by the number of gallons in column G.
If this isn't what you were expecting, please tell me what it IS that you were expecting. You should definitely NOT be seeing the $3.00 rate applied to all of them, which is what I think you said was happening.
- I can get more than one tax rate to apply but not all. I am still intermittently working on the worksheet to troubleshoot this and see if i can ascertain any errors. Like you said, I need to examine what I am doing first.