# Thread: Need a Drop down list to do different calculations

1. ## Need a Drop down list to do different calculations

Hello,

I am trying to create a drop down list with the list of provinces, which I can do through Data Validation, what I want is to able to select from the list of provinces and have it calculate the taxes based on which province I selected. So if the drop down list was in d1, and I select Ontario, I want it to Sum the amounts in D5, D6, and D7 and then multiply by 13% and show that amount in D8. I get the right calculations if use the formula =SUMPRODUCT(D5+D6+d7*0.13), but I can't figure out how to combine all of it together. Any help would be greatly appreciated.

Thank you,

Trevor

2. I am confused as to what you are looking for.

What if you selected Quebec? What cells are we adding up? How do we know which tax to apply? Is it supposed to be looked up elsewhere? Where is your drop down list getting its list from?

I think the formula you wanted for the above is: =SUM(D5,D6,D7)*0.13

3. right, sorry about that. I have been creating all my lists on the sheet 2, and then working on sheet 1. I don't have any set up for this particular scenario. The trick here is that each province will have a different set of calculations, and the result would show in a different spot. So if we use your formula, and I picked Quebec, its actually two separate calculations, so something like

=SUM(D5,D6,D7)*0.05+SUM(D5,D6,D7)*0.09975, but the result would have to show in two different spots. This part -(D5,D6,D7)*0.05 would need to show in D9 and this part (D5,D6,D7)*0.09975 would have to show in D10.

So I would have my list of provinces and territories on Sheet 2 and create the drop down in D1, these are the cells that are totaled: D5,D6,D7,

ON-13% result should display in D8
NS-15% result should show in D8
NB-13% result should show in D8
NFLD- 13% result should show in D8
PEI- 14% result should show in D8
QC- 5% and 9.975% (tried to explain earlier in post) results should show in D9 and D10
MB-5% result should show in D9
SK-5% result should show in D9
AB-5% result should show in D9
BC-5% result should show in D9
YT-5% result should show in D9
NWT- 5% result should show in D9
NU- 5% result should show in D9

thank you for responding, I greatly appreciate the help,

Chappy105

4. See Attached.

I created a table in Sheet2 of Province codes vs tax rates, then used MATCH() checks and VLOOKUP() to extract results.

Formula in D8:

=IF(ISNUMBER(MATCH(D1,Sheet2!\$L\$1:\$L\$5,0)),SUM(\$D\$5:\$D\$7)*VLOOKUP(D1,Sheet2!\$L\$1:\$M\$13,2,0),"")

Formula in D9:

=IF(ISNUMBER(MATCH(D1,Sheet2!\$L\$6:\$L\$13,0)),SUM(\$D\$5:\$D\$7)*VLOOKUP(D1,Sheet2!\$L\$1:\$M\$13,2,0),"")

Formula in D10:

=IF(D1="QC",SUM(\$D\$5:\$D\$7)*VLOOKUP(D1,Sheet2!\$L\$1:\$N\$13,3,0),"")

the MATCH() checks in which group of provinces your D1 selection is, then the VLOOKUP finds appropriate Tax rate or it returns blank.

D10 is special for QC.

5. Originally Posted by NBVC
See Attached.

I created a table in Sheet2 of Province codes vs tax rates, then used MATCH() checks and VLOOKUP() to extract results.

Formula in D8:

=IF(ISNUMBER(MATCH(D1,Sheet2!\$L\$1:\$L\$5,0)),SUM(D57)*VLOOKUP(D1,Sheet2!\$L\$1:\$M\$13,2,0),"")

Formula in D9:

=IF(ISNUMBER(MATCH(D1,Sheet2!\$L\$6:\$L\$13,0)),SUM(\$D\$5:\$D\$7)*VLOOKUP(D1,Sheet2!\$L\$1:\$M\$13,2,0),"")

Formula in D10:

=IF(D1="QC",SUM(\$D\$5:\$D\$7)*VLOOKUP(D1,Sheet2!\$L\$1:\$N\$13,3,0),"")

the MATCH() checks in which group of provinces your D1 selection is, then the VLOOKUP finds appropriate Tax rate or it returns blank.

D10 is special for QC.
Works great, except why do I get a Value error in the empty boxes in column H.

6. Change formula there to:

=IF(D5="",0,G5-D5)

copied down.

when D is blank (formula blank), it is seen as text string.. so you get error trying to do arithmetic on it...

The other way around it is to have my first set of formulas return 0 instead of "" when matches not found.

7. Second wrinkle.....just noticed as I was playing with it......in some scenarios it could be possible that one element would need a different tax rate....for example....

Flight leaves Toronto for Vancouver, the tax located in d7 (SQ) could have two different tax rates. Toronto-\$25.00x13% and Vancouver-\$20.00x5%, or Toronto to Halifax would both charge HST, but at different rates Toronto is 13% and Halifax is 15%, but the 15% is only charged on the SQ tax for Halifax, and but the results would total in D8. So for the later scenario....

Base fare + CA + Toronto SQ x 13% result displayed in D8, then Halifax SQ x 15% added to the total in D8....I am thinking maybe a dependent drop down??? So I am thinking maybe a drop down for the Outbound and a separate for the inbound...if they are the same, then the calculations you already gave me are perfect, but if they aren't.....is her a way to tell it that?

I hate being a bother, so I really appreciate your time helping me with this.

8. awesome thank you!!

9. So do you really need province drop down in D1? Or should you be dealing only with cities. Then have some sort of lookup table for the in/out cities.

10. Wonder if there is a way to do that in my SQ taxes box??? So as I select the appropriate SQ TAX it would apply the appropriate provincial tax.....so say I have the drop down calculate the Base Fare + CA x provincial tax rate, then select my SQ and have it add to the totals we already have there???

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•