Frank, I need your help, PLEASE

B

bianca

I'm trying to create a worksheet that will calculate sales tax for eac
county in my state. Problem is that each county has a different rate
I've created a formula that worked but when I tried to add the last tw
counties, excel said my formula was too long. I don't know how t
shorten it. Some of the counties are multipied by the same number - i
there a way around the problem?

The cell I'm writing the formula for is M8, but I'm going to use th
same in cells M10 and M12.

Can you please take a look and see if I can get around this problem?

Thank you!

Bianc

Attachment filename: sales & use tax wksht.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63035
 
C

Chip Pearson

Bianca,

You should use a VLOOKUP formula instead of nested IF statements.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
H

hgrove

Few sensible people open workbooks posted by people they don't know
More often than not they're unnecessary.

bianca wrote...
I'm trying to create a worksheet that will calculate sales tax for
each county in my state. Problem is that each county has a
different rate - I've created a formula that worked but when I
tried to add the last two counties, excel said my formula was
too long. I don't know how to shorten it. Some of the counties
are multipied by the same number - is there a way around the
problem?
Click to expand...
...

More likely than not your formula looks something like

=IF(OR(CountyName={"North";"Hill";"Lake"}),0.05,
IF(OR(CountyName={"South";"Jefferson"},0.0525,
...
)

and you have 7 different tax rates. Excel has a limit of 7 neste
function calls, which means you can't use arbitrarily many IF call
inside other IF calls.

You should create a 2-column table of county names in the left colum
and corresponding tax rates in the right column. Then use VLOOKUP
Assuming you'd sorted this table by county name in ascending order,

=VLOOKUP(CountyName,CountyRateTable,2)

would return the tax rate corresponding to CountyName
 
Top