Help with IF statement

  • Thread starter Thread starter Dan Grabenstein
  • Start date Start date
D

Dan Grabenstein

My tax planning spreadsheet works fine in Quattro. But to convert it to
Visual Basic I have to translate it to excel. I have begun to do this
BUT, when I calculate the tax, for example, using a table similar to


If taxable income is over: Then regular income tax equals:
Not over $7000 10% of taxable income
Over $7000 but not over $28000 700 plus 15% of amount over 7000
Over $28400but not over $ 68800 3910 plus 25% of amount over 28400
etc

In quattro i use the following:

@if(z67<7000,z67*0.01,@if(z67<=28400#and#z67>=7000,((z67 - 7000)* 0.15)
+ 700,@if(z67<=68800 #and# z67>=28400,((z67-28400)* 0.25 + 3900, and so
on...

This works in quattro but in looking into the help file in excel i
cannot find anything analagous, particularly the "and" (and also "or" )
used in the if statement.

any suggestions??

Thanks,

Dan
 
From the documentation Excel 2000 (one I currently have
loaded) can support up to 7 nested IF statements.

To use Logical AND, OR, NOT - they are themselves
functions so you simply do AND( condition1, condition2...)

Finally an example of how you would write that same
statement is (broken up on the false condition for easier
reading):

=if(z67 < 7000, z67* 0.10,
if( and( z67<=28400,z67 >=7000),((z67-7000)*0.15)+700,
if( and( z67<=68000,z67 >=28400),((z67-28400)*0.25)+ 3900,
and so on...
 
Dan-

The equivalent of a statement like:

@if(z67<=28400#and#z67>=7000

in Excel is:

if(and(z67<=28400,z67>=7000),...

AND and OR are functions in Excel.

But...

I should point out that in Excel (and QP I think) you do not need the AND:

=if(z67<7000,z67*.01,if(z67<=28400,(z67-7000)*.15+700,if(z67<=68800,....

If the amount is less that 7000 the formula never gets to the second if so
there is no need to check for <7000 there, etc.
 
Use a function to calculate the taxable income. See example below.
**********************************************************************

Public Function TaxableIncome(Income As Long) As String

If Income <= 7000 Then
TaxableIncome = Income * 0.1
ElseIf Income > 7000 And Income < 28000 Then
TaxableIncome = 700 + ((Income - 7000) * 0.15)
ElseIf Income > 28400 And Income < 68800 Then
TaxableIncome = 3910 + ((Income - 28400) * 0.25)
Else:
TaxableIncome = "Failed"
End If
End Function

**********************************************************************
 
Back
Top