Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Need a Drop down list to do different calculations

  1. #1

    Need a Drop down list to do different calculations



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,467
    Articles
    0
    Excel Version
    Excel 2016
    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. #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. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,467
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files
    Last edited by NBVC; 2014-05-23 at 06:02 PM.


  5. #5
    Quote Originally Posted by NBVC View Post
    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.
    Last edited by Chappy105; 2014-05-23 at 05:43 PM.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,467
    Articles
    0
    Excel Version
    Excel 2016
    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. #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. #8
    awesome thank you!!

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,467
    Articles
    0
    Excel Version
    Excel 2016
    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. #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 LastLast

Posting Permissions

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