Nesting or And function?

[Utradeshow]

Hi All,

I have a tricky formula that I'm stumped on.

In cell A1 I have a dropdown that is taxable and non taxable
In cell B1 I have a dropdown with 3 counties Broward (6%), Dade (7%), Palm Beach (7%)
In cell C1 i have a sale amount and I am trying to calculate the tax rates with the result in cell D1.

If cell A1 says "Taxable" then the result in cell D1 would calculate based on the value of B1.

Thanks a Bunch!!

 

[Jonmo1]

Try

=IF(A1="taxable",B1*C1,A1)

 

[Utradeshow]

I tried to make a mockup and explain it, but I couldn't apparently. I have been messing with this and think I'm close. However, This formula seems to work only sometimes? Is that even Possible?

Here is the exact formula, it doesn't seem to catch the A2 value sometimes?

IF(AND(A2 = "RED Dyed Ultra Low Sulfer",A8="Service County - Broward"),0.06,IF(A8="Service County - Dade",0.07,IF(A8="Service County - Palm Beach",0.07,0)))

Anything other than RED Dyed Ultra Low Sulfer in A2 should return 0

 

[Jonmo1]

Perhaps

=IF(A2="RED Dyed Ultra Low Sulfer",IF(A8="Service County - Broward",0.06,IF(A8="Service County - Dade",0.07,IF(A8="Service County - Palm Beach",0.07,0))),0)

 

[Utradeshow]

Perhaps

=IF(A2="RED Dyed Ultra Low Sulfer",IF(A8="Service County - Broward",0.06,IF(A8="Service County - Dade",0.07,IF(A8="Service County - Palm Beach",0.07,0))),0)

That works beautifully!! Thank you, I will try to compare the two to see where I went wrong. Only the Broward County option was working properly for some reason.

 

[Jonmo1]

You're welcome.

 

[Utradeshow]

Actually, I just learned I need to raise the bar.... Can I have another set of options if A2 = another value

Current Formula:
IF(A2="Ultra Low Sulfer Diesel",IF(A8="Service County - Broward",0.06,IF(A8="Service County - Dade",0.07,IF(A8="Service County - Palm Beach",0.07,0))),0)

Need to add this set if possible

IF(A2="REC 90 or non ethanol gasoline",IF(A8="Service County - Broward",0.194,IF(A8="Service County - Dade",0.174,IF(A8="Service County - Palm Beach",0.194,0))),0)

I need to join these two formulas if possible

 

[dreid1011]

Perhaps this:

=IF(A2="Ultra Low Sulfer Diesel",IF(A8="Service County - Broward",0.06,IF(OR(A8="Service County - Dade",A8="Service County - Palm Beach"),0.07,0)),IF(A2="REC 90 or non ethanol gasoline",IF(A8="Service County - Dade",0.174,IF(OR(A8="Service County - Broward", A8="Service County - Palm Beach"),0.194,0)),0))

With a couple of ORs to save an IF or two.

 

[Utradeshow]

Would it be possible to add to this function, I want to return the same result as "IF(B3="REC 90 (Non Ethanol Gasoline)" if "Unleaded Gasoline" is the value of B3 also.

=IF(B3="REC 90 (Non Ethanol Gasoline)",IF(B9="Service County - Dade",0.183,IF(OR(B9="Service County - Broward",B9="Service County - Palm Beach"),0.203,0)),IF(B3="Ultra Low Sulfer Diesel",IF(B9="Service County - Dade",0.153,IF(OR(B9="Service County - Broward",B9="Service County - Palm Beach"),0.153,0)),0))

 

[dreid1011]

So B3 can equal "REC 90 (Non Ethanol Gasoline)" or "Unleaded Gasoline"? If so, try the following modification:

=IF(OR(B3="REC 90 (Non Ethanol Gasoline)",B3="Unleaded Gasoline"),IF(B9="Service County - Dade",0.183,IF(OR(B9="Service County - Broward",B9="Service County - Palm Beach"),0.203,0)),IF(B3="Ultra Low Sulfer Diesel",IF(B9="Service County - Dade",0.153,IF(OR(B9="Service County - Broward",B9="Service County - Palm Beach"),0.153,0)),0))