Forum Discussion
What is best formula for this tax rate calculation?
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.
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
But If I change the format to plain numbers, it is this:
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.