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.
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
- Re: [libreoffice-users] Calc Function Wizard - IF Result differences (continued)
Impressum (Legal Info)
: 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