Help with long formula - how do it shorten?

B

bianca

I'm trying to create a worksheet that will calculate sales tax. I'v
created a drop-down list of all counties in my state. The formula wil
depend on which county someone lives in. This is the formula that I'v
created...problem is, I was trying to enter my last two counties an
excel said my formula was too long. As you can see, some of th
counties are multiplied by the same number. I need to differentiat
counties on my drop-down but no one needs to see the formula so long a
it calculates correctly (I can combine counties together is what I'
saying).

=SUM(G8*0.0725)+(H8*0.005)+(I8*0.01)+(J8*0.005)+(K8*0.005)+(IF(L7=Alameda,PRODUCT(L8*0.005),IF(L7=ContraCosta,PRODUCT(L8*0.01),IF(L7=Placerville,PRODUCT(L8*0.0025),IF(L7=Fresno,PRODUCT(L8*0.00625),IF(L7=Clovis,PRODUCT(L8*0.0003),IF(L7=Imperial,PRODUCT(L8*0.005),IF(L7=WestSacramento,PRODUCT(L8*0.005)))))))))+(IF(L7=Inyo,PRODUCT(L8*0.005),IF(L7=Clearlake,PRODUCT(L8*0.005),IF(L7=Madera,PRODUCT(L8*0.005),IF(L7=Mariposa,PRODUCT(L8*0.005),IF(L7=Willits,PRODUCT(L8*0.005),IF(L7=Woodland,PRODUCT(L8*0.005),IF(L7=Napa,PRODUCT(L8*0.005)))))))))+(IF(L7=Nevada,PRODUCT(L8*0.00125),IF(L7=Truckee,PRODUCT(L8*0.005),IF(L7=Sacramento,PRODUCT(L8*0.005),IF(L7=SanFrancisco,PRODUCT(L8*0.0125),IF(L7=SanJoaquin,PRODUCT(L8*0.005),IF(L7=SanMateo,PRODUCT(L8*0.01))))))))+(IF(L7=SantaBarbara,PRODUCT(L8*0.005),IF(L7=SantaClara,PRODUCT(L8*0.01),IF(L7=SantaCruz,PRODUCT(L8*0.0075),IF(L7=Solano,PRODUCT(L8*0.00125),IF(L7=Sonoma,PRODUCT(L8*0.0025),IF(L7=Sebastopol,PRODUCT(L8*0.00125))))))))+(IF(L7=Stanislaus,PRODUCT(L8*0.00125)))

Can anyone help?

Thank you!

Bianc
 
F

Frank Kabel

Hi Bianca
Reason fopr this is the maximum of 7 nested functions for Excel.
Instead try the following:
1. Set-up a sheet with countries and associated value. e.g. call this
sheet 'data' and use the following layout:
A B
1 Alameda 0.005
2 Contracosta 0.01
.....


2. After this use the following formula
=(G8*0.0725)+(H8*0.005)+(I8*0.01)+(J8*0.005)+(K8*0.005)+(L8*VLOOKUP(L7,
'data'!$A$1:$B$30,2,0))

Note: No need for the SUM function. also your PRODUCT function is not
required.
 
A

Aladin Akyurek

Looks like you have a dropdown list of counties in L7 from which the user
makes a choice. If so, expand the 1-column list of counties with the
associated percentages in the next column. Sort this 2-column list in
ascending order and name the selection COUNTIES. You might want to change
the list source in L7 to:

=INDEX(COUNTIES,0,1)

After you have done that, you can invoke the following formula to calculate
the desired tax:

=SUMPRODUCT(G8:K8,{0.0725,0.005,0.01,0.005,0.005})+LOOKUP(L7,COUNTIES)*L8
 
B

bianca

You're a life-saver! Thank you! I've been trying to get this stupi
thing to work for three days
 
S

Soo Cheon Jheong

Bianca,

1) Create a data table in Activesheet
-----------P------------Q-----------
1
2 Alameda 0.005
3 Clearlake 0.005
4 Clovis 0.0003
5 ContraCosta 0.01
6 Fresno 0.00625
7 Imperial 0.005
8 Inyo 0.005
9 Madera 0.005
10 Mariposa 0.005
11 Napa 0.005
12 Nevada 0.00125
13 Placerville 0.0025
14 Sacramento 0.005
15 SanFrancisco 0.0125
16 SanJoaquin 0.005
17 SanMateo 0.01
18 SantaBarbara 0.005
19 SantaClara 0.01
20 SantaCruz 0.0075
21 Sebastopol 0.00125
22 Solano 0.00125
23 Sonoma 0.0025
24 Stanislaus 0.00125
25 Truckee 0.005
26 WestSacramento 0.005
27 Willits 0.005
28 Woodland 0.005
------------------------------------

2) Use the following formula:

=G8*0.0725 + H8*0.005 + I8*0.01 + J8*0.005 + K8*0.005
+ VLOOKUP($L$7,$P$2:$Q$28,2,FALSE)*L8


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
D

Dana DeLouis

I see you have a solution. If you would like, your first 5 terms...
=SUM(G8*0.0725)+(H8*0.005)+(I8*0.01)+(J8*0.005)+(K8*0.005) +

could be written like this if you want
=SUMPRODUCT(G8:K8,{0.0725,0.005,0.01,0.005,0.005}) +
'or
=(29*G8 + 2*H8 + 4*I8 + 2*J8 + 2*K8)/400 +
'or even
=(29*G8 + 2*(H8 + 2*I8 + J8 + K8))/400 +

HTH
Dana DeLouis
 
D

Dana DeLouis

Actually, I think I like this one better:

=SUMPRODUCT(G8:K8,{72.5,5,10,5,5})/1000 +

Dana DeLouis said:
I see you have a solution. If you would like, your first 5 terms...
=SUM(G8*0.0725)+(H8*0.005)+(I8*0.01)+(J8*0.005)+(K8*0.005) +

could be written like this if you want
=SUMPRODUCT(G8:K8,{0.0725,0.005,0.01,0.005,0.005}) +
'or
=(29*G8 + 2*H8 + 4*I8 + 2*J8 + 2*K8)/400 +
'or even
=(29*G8 + 2*(H8 + 2*I8 + J8 + K8))/400 +
Click to expand...

<snip>
 
Top