Forum Discussion
What is best formula for this tax rate calculation?
Using named ranges, I am trying to multiply the proper tax rate to gallons of soft drink product. I am working within a pivot table. The rate used depends on what I'll call carbonation level (let's say, 5%, 10% and 15%). So, we have product name in column 1, product gallons in column 2, carbonation level in column 3 and calculated tax in column 4. I expect to use font color filtering and the subtotal function to limit the sum to the products in red font. I've tried PRODUCT, SUMIF, IFS, PRODUCT(IFS) and SUMPRODUCT functions. I am trying to use a single formula in column 4 to calculate the tax for each row (product) according to the tax rate determined by the carbonation level for that product. How would I accomplish this without using a separate formula for each tax rate?
11 Replies
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.
- 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.
