What is best formula for this tax rate calculation?

Copper Contributor

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

@regr250 

 

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.

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.

@regr250 

 

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. 

@regr250 

 

I think you may be entering those "single percentages" incorrectly (or inconsistently). If the figure is displaying with a % sign next to it (automatically doing so), then the underlying number for what looks like 10% is in fact 0.1.

Here's what I mean: the table in the sheet I sent you looks like this

mathetes_0-1599002347743.png

 

But If I change the format to plain numbers, it is this:

mathetes_1-1599002396518.png

 

So if you're doing the lookup with reference to a whole number greater than 1, it will indeed give you the result of $3.00, but that's because the 100% number in the table is just lil' ol' 1.

@regr250 

 

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! 

@regr250 

 

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.