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.
Thanks,
Paul
--
Using OOo 2.0 on Win XP sp2.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
