Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

I still think the VLOOKUP function can help tremendously but it does, in practice, need one or two more columns in the lookup table.

The table as it exists has the number of units in each range, rather than the values at the breakpoints. These breakpoint values are vital, as they are what are needed to make VLOOKUP work in the first place. They can be calculated, based on the units per range, but the actual numbers must be in the first column of the lookup table.

The other column that would help immensely is the summed amount for all the lower-level ranges. The best illustration I can think of from the US income tax calculation:

Looking at only the rates for income over $100,000 (for smaller amounts the tax is looked up in a table rather than calculated), for one filing status, we see that the marginal tax rate is:

    25% for taxable income > $100,000 and <= $142,700
    28% for $142,700 < income <= $217,450
    33% for $217,450 < income <= $388,350
    35% for income > $388,350

In the past the worksheets would have shown the total tax at the top of the previous bracket, plus the rate on the income above the threshold. For example, if the income was $150,000 the worksheet would have said $27,729 + 28% of the amount over $142,700. This can be replicated in the lookup table.

The worksheets now start with the marginal rate and subtract another value representing the difference in rates for the lower income. That same range now says 28% minus $12,221. The answer is the same, just arrived at differently -- and can still be replicated in the lookup table.

I'm not sure how all this fits into the 12-month consideration introduced after the original question, but I hope it explains how VLOOKUP can make the conditionals a lot easier -- if not for this particular case, then for someone else with a similar question.

Dave Liesse

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.