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))
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
