Re: [libreoffice-users] Calc Function Wizard - IF Result differences

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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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