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
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