On Wed, 22 Feb 2006 11:43:55 +1100, Ross Johnson wrote:
> On Tue, 2006-02-21 at 10:38 -0500, Paul_B wrote:
>> On Tue, 21 Feb 2006 13:57:40 +1100, Ross Johnson wrote:
>>
>>> On Mon, 2006-02-20 at 20:44 -0500, Paul_B wrote:
>>>> On Tue, 21 Feb 2006 12:02:13 +1100, Ross Johnson wrote:
>>>>
>>>>> On Mon, 2006-02-20 at 18:52 -0500, Paul_B wrote:
>>>>>> On Sun, 19 Feb 2006 20:27:22 -0500, Paul_B wrote:
>>>>>>
>>>>>>> On Mon, 20 Feb 2006 09:31:28 +1100, Ross Johnson wrote:
>>>>>>>
>>>>>>>> On Sun, 2006-02-19 at 15:29 -0500, Paul_B wrote:
>>>>>>>>> On Sun, 19 Feb 2006 10:59:58 -0600, Rod Engelsman wrote:
>>>>>>>>>
>>>>>>>>>> Paul_B wrote:
>>>>>>>>>>
>>>>>>>>>>>Is there any way to set up a multiple case logic test?
>>>>>>>>>>>
>>>>>>>>>>>Currently I have =IF(B27="";"";B27-(14.325+(E$25-70.35)*0.28)) as
>>>>>>>>>>>a formula in a column of cells. It tests another column's cell,
>>>>>>>>>>>if it's empty the result is null, otherwise a tax approximation
>>>>>>>>>>>kicks in.
>>>>>>>>>>>
>>>>>>>>>>>However, I'd like to allow for a few tax brackets. To do so I'd
>>>>>>>>>>>need to account for the null possibility plus three other
>>>>>>>>>>>possible number ranges (as income increases, a different tax
>>>>>>>>>>>formula should be invoked).
>>>>>>>>>>>
>>>>>>>>>>>Currently I don't see how this is possible.
>>>>>>>>>>>
>>>>>>>>>>>Thanks,
>>>>>>>>>>>paul
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>> You can use nested IF() statements.
>>>>>>>>>>
>>>>>>>>>> =IF(B27="";"";IF(B27=.......
>>>>>>>>>
>>>>>>>>> Ahh. Ok, thanks. Hadn't thought of that. A case select command
>>>>>>>>> would be nice, but this should do what I need.
>>>>>>>>
>>>>>>>> Have you tried using lookup tables for this kind of thing.
>>>>>>>> Functions: LOOKUP, VLOOKUP etc in Help.
>>>>>>>>
>>>>>>>> I use them for this purpose in my own tax sheets and they work.
>>>>>>>
>>>>>>> That indeed looks like it would do it, as it amounts to a
>>>>>>> powerful case select statement. Should be interesting working out
>>>>>>> the details. Will get on this maybe tomorrow.
>>>>>>>
>>>>>>> Thanks much,
>>>>>>> Paul
>>>>>>
>>>>>> Ok, I'm trying to set this up.
>>>>>>
>>>>>> Is there any way to use a function's return as part of a cell
>>>>>> address?
>>>>>>
>>>>>> For instance:
>>>>>>
>>>>>> =MATCH($E$26;$H$3:$H$8)
>>>>>>
>>>>>> finds the row in which the H column matches cell E26. but
>>>>>>
>>>>>> =G(MATCH($E$26;$H$3:$H$8))
>>>>>>
>>>>>> doesn't work. I want to find the row which contains the value
>>>>>> searched for, and then use that number in the address of another
>>>>>> value, here found under column G.
>>>>>
>>>>> Use something like =INDIRECT("G"&MATCH($E$26;$H$3:$H$8)+2)
>>>>>
>>>>> But LOOKUP does this for you. E.g.
>>>>>
>>>>> = LOOKUP($E$26;$H$3:$H$8;$G$3:$G$8)
>>>>
>>>> You definitely know your stuff, The INDIRECT formula is awesome,
>>>> and you even caught the need for an offset of 2 in my MATCH
>>>> formula.
>>>>
>>>> I was getting LOOKUP to return the row number, as does your
>>>> formula above, but only because I set up column G to contain that
>>>> data. But how then would I use the returned row number to specify
>>>> which cells to poll for the tax data? That's where I'm stuck.
>>>>
>>>> Using the INDIRECT formula, I came up with
>>>>
>>>> =IF(B26="";"";B26-( INDIRECT("i"&MATCH($E$25;$H$3:$H$8)+2)
>>>> +INDIRECT("j"&MATCH($E$25;$H$3:$H$8)+2)*($E$25-LOOKUP(E$25;$H$3:$H$8;$H$3:$H$8))))
>>>>
>>>> which successfully takes the income in B26 and then subtracts off
>>>> the base tax and incremental tax bracket tax. But this is very
>>>> unwieldy. If LOOKUP can do this directly, it would be a big
>>>> improvement.
>>>
>>> Hey. Compliments always work :)
>>>
>>> I guess this has turned into a bit of a tutorial on lookup tables now.
>>>
>>> I assume your tax brackets are structured the same as mine, i.e a table
>>> like this (top left at cell H3):
>>>
>>> 0 9999 0 0 Tax free threshold
>>> 10000 19999 0 0.15 $0 plus $0.15 for each $ over $10000
>>> 20000 39999 1500 0.25
>>> 40000 59999 6500 0.30
>>> 60000 89999 9500 0.35
>>> 90000 20000 0.45 $20000 plus $0.45 for each $ over $90000
>>>
>>> $90000 is the top threshold. If you don't have a tax-free threshold
>>> where you are then you can see how the table would change.
>>>
>>> The second column isn't actually needed in the formulae.
>>>
>>> Tax:
>>> =IF(B26<0;0;LOOKUP(B26;$H$3:$H$7;$J$3:$J$7)
>>> +(B26-LOOKUP(B26;$H$3:$H$7;$J$3:$J$7))
>>> *LOOKUP(B26;$H$3:$H$7;$K$3:$K$7))
>>>
>>> Of course, the formula is a lot simpler if you store the base tax amount
>>> for your income in another cell.
>>>
>>> Base Tax:
>>> B27 =LOOKUP(B26;$H$3:$H$7;$J$3:$J$7)
>>>
>>> Tax:
>>> =IF(B26<0;0;B27+(B26-B27)*LOOKUP(B26;$H$3:$H$7;$K$3:$K$7))
>>>
>>> I give no warranty that these formulae won't get you a jail term :)
>>
>> Wow, it's so simple once I see it. I was missing the obvious step
>> of looking up the tax bracket floor again and subtracting it from
>> the actual income, in preparation for multiplication by the
>> incremental rate.
>>
>> Thanks very much for taking the time to lay this out. Hopefully
>> others have gained from it as much as I.
>
> Thanks for spotting the error.
> I incorrectly subtracted the base tax from the income instead of the
> income threshold for that tax. The simplified version I gave is also
> invalid then.
>
> The formula should be (for completeness):
>
> Tax:
> =IF(B26<0;0;LOOKUP(B26;$H$3:$H$8;$J$3:$J$8)
> +(B26-LOOKUP(B26;$H$3:$H$8;$H$3:$H$8))
> *LOOKUP(B26;$H$3:$H$8;$K$3:$K$8))
Ok, I neglected to define one more parameter on my sheet. B26
here is pre-tax net worth, rather than income, while E26 is the
current level of capital gains, which is what the tax exclusively
will be based on. After I determine the tax on capital gains I
subtract it from B26 to get current net worth, tax liability
inclusive. Sorry for the confusion.
Thanks so much for your help. It appears to be working fine here,
and I've learned a lot. Very powerful tool.
Paul
--
Using OOo 2.0 on Win XP sp2.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
