2013 Archives by date, by thread · List index

# 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
```