Need a Drop down list to do different calculations

Chappy105

New member
Joined
May 22, 2014
Messages
9
Reaction score
0
Points
0
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
 

Attachments

  • Exchange grid v4.0.xlsx
    113.2 KB · Views: 27
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
 
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
 
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.
 

Attachments

  • Exchange grid v4.0.xlsx.XLS
    113.9 KB · Views: 45
Last edited:
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(D5:D7)*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.
 
Last edited:
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.
 
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.
 
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.
 
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???
 
not really sure what you mean.... :confused:

Sorry, I am thinking out loud....if you look in the bottom right corner of sheet 1,you will see a box "SQ Taxes box", in this box I used VLookup to find the different SQ taxes for the different airports. So my thought is....maybe still have the drop down in D1, and have it calculate the taxes for D5 and D6, then have it total in the appropriate tax box. Example: Selected ON from the drop down, that would take the amounts from D5+D6 x 0.13, then put that amount in D8. Next, in SQ Taxes box, I could select the cities that I'm departing from, s say I enter YOW in the City Code (J23), which will populate 23.00 in J24 and in D7. I am wondering if there is a way to make it calculate the right taxes from this box, and then add it into the appropriate tax box in the Exchange Grid part. If ya see where I am going with that.....
 
Hopefully this is what you mean....

In Sheet2, In column J add the appropriate tax rates for each city code.

Then sort columns H:J by column H in ascending alphabetic order.

Now formula in D8 of sheet1:

=IF(ISNUMBER(MATCH(D1,Sheet2!$L$1:$L$5,0)),SUM(D5:D6)*VLOOKUP(D1,Sheet2!$L$1:$M$13,2,0)+(SUMPRODUCT(LOOKUP(Table4[City Code],Cities,Sheet2!$I$1:$I$62),LOOKUP(Table4[City Code],Cities,Sheet2!$J$1:$J$62))),"")
 

Attachments

  • Exchange grid v4.0.xlsx
    114 KB · Views: 9
Hopefully this is what you mean....

In Sheet2, In column J add the appropriate tax rates for each city code.

Then sort columns H:J by column H in ascending alphabetic order.

Now formula in D8 of sheet1:

=IF(ISNUMBER(MATCH(D1,Sheet2!$L$1:$L$5,0)),SUM(D5:D6)*VLOOKUP(D1,Sheet2!$L$1:$M$13,2,0)+(SUMPRODUCT(LOOKUP(Table4[City Code],Cities,Sheet2!$I$1:$I$62),LOOKUP(Table4[City Code],Cities,Sheet2!$J$1:$J$62))),"")

Thank you again, I tried playing with this, but I am not getting the results I was looking for, I just think their are too many variables. So I am thinking a different approach may the way to go. I have attached an updated spread sheet, and here is my thought and I am hoping you can help. I am thinking series of Dynamic drop downs with different calculation options for each row. If you check sheet 2 I have added the possible situations and the tax rates that could be applied. So first drop down would be Yes/No (does this tax apply, sheet 2 column K), second drop down would be the different situations that the tax could be applied (Sheet 2 Column L), 3rd Drop down would be the tax rate that applies to the given situation and the forth would be for the I/B SQ tax, for when the tax rates on the SQ's are different (Sheet 2 Columns O and P).

So for each tax their are 4 or 5 possible ways that each tax could be applied,

RC TAX (Row 7) 13%14%,15%

1. Tax doesn't' apply to the reservation
2. Tax applies to everything (D4+D5+d6 x tax rate, show result D7)+(E4+E5+E6 x Tax rate, Show result in E7) + (F4+F5+F6 x Tax rate, show result in f7)
3. Tax applies to almost everything (D4+D5+E6 x tax rate, show result in D7)+(E4+E5+E6 x tax rate, show result in E7)+(F4+F5 x tax rate, show result in F7)
4. Tax apples I/B SQ only (F6 x tax rate, show result D7 and F7)
5. Tax rates are different (( D4+D5+E6 x tax rate 1) +( D4+D5+F6 x tax rate 2) add both totals into D7)+(E4+E5+E6 X O/B Tax rate 1, show results in E7) + (F4+F5+F6 X I/B Tax rate 2, show results in F7)

XG TAX (Row 8) 5%

1. Tax doesn't' apply
2. Tax applies to everything (D4+D5+D6 X Tax Rate, how result in D8) + (E4+E5+E6 X Tax rate, show result in E8) +(F4+F5+F6 X Tax rate, show result in F8)
3. Tax applies to almost everything (D4+D5+E6 x Tax Rate show result in D8)+ (E4+E5+E6 x tax rate, show result in E8)+(F4+F5 x tax rate, show result in F8)
4. Tax apples I/B SQ only (F6 x tax rate, show result D8 and F8)

XQ TAX (Row 9) 9.975%

1. Tax doesn't apply
2. Tax applies to everything (D4+D5+D6 x Tax Rate, show results in D9) + (E4+E5+E6 x Tax Rate, show result in E9)+ (F4+F5+F6 x Tax Rate, show result in F9)
3. Tax applies to almost everything (D4+D5+E6 x Tax Rate, show results in D9) + (E4+E5+E6 X Tax rate, show result in E9) + (F4+F5 x Tax Rate, show result in F9)
4. Tax applies to I/B SQ Only (F6 x Tax Rate, show result in D9 and F9)

So long story short, is there a way to do that?? Thank you for all your help, I will have to buy you a virtual beer!!
 
Sorry here is the new grid.
 

Attachments

  • Exchange Grid 5.0.xlsx
    18.1 KB · Views: 13
Back
Top