Quick excel help needed
 

Need an IF THEN OR formula for excel.

I need to calculate sales tax for 3 specific states, but not the other 47, that will be identified as NY, SC, OH so I need a formual that says "if (cell x) = NY then calculate sales tax based on the total amount column (cell y), else = 0

Make sense?

THX,
SCOTT

I see what you're trying to do and am also interested in the answer as I haven't used those commands since my class in BASIC 20 years ago:D

One would think it would simply be. IF C6 = NY then (F6*1.08) or whatever, but I doubt it's that obvious. Then ofcourse you need the "otherwise (F6*1).

I believe what you're looking for is actually an IF-THEN-ELSE function, or a "Nested IF" function.

Randy

Did you try the HELP function? for conditkonal IF,Then




A

15
9
8


Formula Description (Result)
=IF(A2=15, "OK", "Not OK") If the value in cell A2 equals 15, then return "OK". (OK)
=IF(AND(A2>A3, A2<A4), "OK", "Not OK") If 15 is greater than 9 and less than 8, then return "OK". (Not OK)
=IF(OR(A2>A3, A2<A4), "OK", "Not OK") If 15 is greater than 9 or less than 8, then return "OK". (OK)


The IF function uses the following arguments.



Formula with the IF function

logical_test: the condition you want to check

value_if_true: the value to return if the condition is true

value_if_false: the value to return if the condition is false

Function details
AND

OR

IF

Microsoft excel :tdown:

Sorry, I'm working on little sleep.

Here's the formula,

IF(j2=10,"=sum(19.99*.0875)",0)

This works, the only problem I have is when you replace "10" with text "NY" I get #NAME as the output and it almost feels like the cell is not formatted properly.

= IF(logical test, value if true, value if false)

EDIT:
I'm thinking, =IF(OR(NY,SC,OH),(taxrate*total amt), 0)

you can just use the cell definition for the taxrate i.e. B4 and the cell definition fr the total i.e. G7.

I did something similar, and it worked pretty well.

sjd

there's a couple of ways around it. Here's one to build on:

=IF(C11="ny",0.05,IF(C11="sc",0.06,IF(C11="oh",0.0 7,0)))


(Ohio has a high tax rate).

=IF(OR(A1="NY",A1="SC",A1="OH"),D1*E1, 0)

proven

sjd

here's the data group.

NY 1665 45 37
SC 1665 45 37
OH 1665 45 37
CA 0 45 37
FL 0 45 37
OH 1665 45 37
SC 1665 45 37

=IF(J2="ny",(19.99*0.0875),IF(J2="oh",(19.99*0.065 ),IF(J2="sc",(19.99*0.05))))

Change the 0.065 and 0.05 values for oh and sc (respectively) to whatever the correct numbers are.


Randy

http://office.microsoft.com/en-us/excel/HA012186941033.aspx?pid=CL100570551033

here's a link to returning multiple values from multiple inputs.

sjd

Randy, that works perfectly, one thing, if the 3 conditions do not match (one of the other 47 states) it returns "False" which would be fine but I need to drop this into a database that will not accept false, can we make the formula spit out a value of 0?

Mine does.

Basically you want to do something like

=IF([cell reference]="NY",[formula w/ sales tax],[formula w/o sales tax])

For example

=IF(A10="NY",B10*1.06,B10)

But you have to charge sales tax in three states, NY SC and OH? If it is the same tax rate in all states, then try

=IF(OR(A10="NY",A10="SC",A10="OH"),B10*1.06,B10)

If there is a different tax rate in each state, you can used nested IFs or a VLOOKUP table.

Nested IFs would be like

=IF(A10="NY",B10*1.06,IF(A10="SC",B10*1.065,IF(A10 ="OH",B10*1.07,B10)))

Nested IFs are ugly, so personally I would set up a 2-col x 3-row table with NY SC OH down one column and 1.06 1.065 1.07 down the other column. Suppose that table is located in cells I23:J25. Then

=IF(ISERROR(VLOOKUP(C23,$I$23:$J$25,2,FALSE)),1,VL OOKUP(C23,$I$23:$J$25,2,FALSE))

will give you 1.06 if C23 is "NY", 1.065 if it is SC, 1.07 if it is OH, and 1 if it is anything else.

The VLOOKUP approach lets you easily change the tax rates, add more states to tax, etc, and is way easier to read and debug than yucky nested IFs.

P.S. Wow, no shortage of responses, all posted while I was typing mine. Randy's formula needs a term telling it what to return if none of the IFs are true, i.e. if the state is not NY SC or OH, that is probably why you are getting the "False".

PM me your email and I will email you the spreadsheet I did to test the VLOOKUP method I posted.

Thanks for the help guys.

I need to get some rest and do this again tomorrow. Running on 4 hours sleep in the last 48 hours.

I need to double check my sheet before I charge 200 orders to the wrong customers.

Word to the wise. If you are doing ecomm and need a merchant account use authorize.net Use them for 5 other sites and you can customize the order of the info in your batch file.

This project is using a different merchant account that has a strict format for the order of the fields but the data coming in from the call center does not match so I need to redo each batch file.

Try this one, Scott!

=IF(J2="ny",(19.99*0.0875),IF(J2="oh",(19.99*0.065 ),IF(J2="sc",(19.99*0.05),0)))

Cheers!

Randy

Get some sleep, you're way past the point of diminishing returns.