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.
- No, but that gives me some direction to research. I can probably upload the spreadsheet to give you some idea and maybe also the best idea I have for a formula so far. Thanks.
