Forum Discussion
What is best formula for this tax rate calculation?
Are you familiar with the various LOOKUP functions? Or INDEX and MATCH?
In general, tax tables are accessed by means of one of the various lookups such that they can access the row or cell of the tax table that is appropriate based on some of those other variables you speak of.
Without actually seeing your layout (despite your description) it's hard to give you an exact formula. Always more helpful to the helpers here if you can post a copy of your spreadsheet, just devoid of any private or confidential info.
Here's the way you can use VLOOKUP....I do recommend going to that exceljet source to read up on how VLOOKUP works. In this case, I'm using the "inexact match" method, since the percentages are not always precisely what the table reflects. In that case, the function goes to the table and finds the first figure that exceeds the referenced value and then falls back to the preceding value.
mathetes I think that VLOOKUP puts me on the right path but so far it does not return the results I need if single percentages are used. The tax rates are dependent on the range of carbonation in the beverage. 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. I've tried to use the < and > operators but never with VLOOKUP. How would I overcome this specifically? Right now, I am looking at how VLOOKUP would be used to calculate grades since the numeric grade will always fall within a range.
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).