4 Taxing By Province
Explication de la tâche .
A local food store has been selling online and was stuck trying to figure out how to tax everyone based on their location!
They gave you a list of sales they did but the system online doesn't extract the taxes from the sales.
First, could you find the appropriate Tax Amount for each of these sales!
Secondly, could you figure out which sales are the most popular. The ones under 10$, the ones in between 10$ and 60$ or the ones over 60$?
🌶 Défi épicé . 🌶
Comment faire.
This exercice will test your VLOOKUP skills in 2 different ways. You will need to create to VLookup Functions to achieve these goals and use some pivot tables to find the most popular amount or the COUNTIF formula!
Alright so step 1 of creating any VLOOKUP is to name the range of your reference table! To do this you select the tax table in the second sheet and name it "TAXTABLE" - Once that's done don't forget to hit ENTER
Now, we've named the table it will be easy to make the VLOOKUP in the other sheet to find all the appropriate taxes
Type Tax % as a new collumn name and go in the first cell and type your VLOOKUP like this :
=VLOOKUP(E2,TAXTABLE,5,0)
Here is an attempt to explain what all the arguments of this functions mean :
E2: The cell we are looking for in the reference table
TAXTABLE: The reference Table (Previously named)
5: The fifth collumn starting from the left
0: A 0 is required at the end to make the lookup function find EXACT matches (I.E: the exact same text string)
This will create all the apt Tax percentages in it's own collumn but it won't be the tax amounts. To get that running you will need to change the title of your collumn to "Tax Amount" and multiply your function by the price - Should look something like this :
=VLOOKUP(E2,TAXTABLE,5,0)*D2
Hell yeah! We found what amazon couldn't for so many years! The regional taxes
Now that this is done you will need to get another VLOOKUP to find the amounts for each category!
To do this you must type the categories in a table and name it. You can view the gif to give you an idea of how the table needs to be done and named.
Another Table named! Time now to Input the new VLOOKUP!
=VLOOKUP(D2,CatTable,2)
This time we did not put a 0 at the end because we want our function to look in between the numbers not just the exact numbers. We call this an approximative match VLOOKUP!
Once this is done you should be able to use pivot tables or Countif to find which was the most popular. Here is what Pivots would look like:
And where what it would look like to run it with countif's
