I need some help with finding the mean value of a range of numbers on a given table.
I have a Federal, and nine provincial/territory tax table files (Canada other than Quebec) of which I will be looking up this mean value. With each file comes one of four payroll schedules (weekly, biweekly, semi-monthly, monthly). And each table spans several pages in the pdf files provided by the CRA (Canada Revenue Agency).
As a note, these tables are available to the public on their website.
The CRA has provided the public a document that gives the formulas to calculate payroll tax based on your province/territory and the payroll schedule and salary. I have confirmed my program works with these formulas, using vba, with the CRA. This is not necessarily the issue.
To use the tables, the user first needs to know the Claim Code for their salary. With the correct table and their taxable income amount, the user will find a range of two numbers, cross reference that number's range with the Claim Code.
With the CRA formulas, the Claim Codes are removed from the equation and we should only concern ourselves with the number range given.
Example:
If I was looking up a weekly salary in Manitoba for 3195, we would find the range 3184-3216.
What the table will do is use the mean value for this range (which would be 3200) and calculate the income tax based on that amount, not 3195.
What I would like to do is remove the need to look up any of the tables. Before choosing the calculate the tax for the amount given, the user will have two options of "Use Tax Table" and "Use CRA Formulas". If the user chooses the first option, I would like the user to enter the number as 3195 and the result will be for the mean value of 3200.
These tables are provided as pdf files (one for each province/territory), and each includes the federal and well as its respective tables for each of the pay schedules.
IF it were possible to extract the information from the PDFs and get the proper mean value, then I would ask for directions to do that along with coding to find the mean and so on. The problem with this is when I decide to include Quebec in the calculations, the PDF file is ... well different.
I haven't been able to save it as a text file and have all of the tables appear as they should. So I think for now, if I have to go into each PDF and copy the first two colums out and add them to Excel, I would be more than willing to do so. I just need to find out what to do after that.
The point of this exercise is that our payroll class needs to look up the tax amounts on the table with the claim codes with each assignment, which they should learn how to do. As an instructor, working on each class assignment, I see the formulas as the most efficient way to spend my time (less time spent looking up the tables, the more assignments I can give my class). Enter the income, and out comes the tax amounts. The problem is my current formula results, although accurate, will not match the class's table amounts... And this is where the above coding would be nice to have.
Thank you to all that read this.
Thank you very much to those who will attempt to solve the puzzle.
-- g
I have a Federal, and nine provincial/territory tax table files (Canada other than Quebec) of which I will be looking up this mean value. With each file comes one of four payroll schedules (weekly, biweekly, semi-monthly, monthly). And each table spans several pages in the pdf files provided by the CRA (Canada Revenue Agency).
As a note, these tables are available to the public on their website.
The CRA has provided the public a document that gives the formulas to calculate payroll tax based on your province/territory and the payroll schedule and salary. I have confirmed my program works with these formulas, using vba, with the CRA. This is not necessarily the issue.
To use the tables, the user first needs to know the Claim Code for their salary. With the correct table and their taxable income amount, the user will find a range of two numbers, cross reference that number's range with the Claim Code.
With the CRA formulas, the Claim Codes are removed from the equation and we should only concern ourselves with the number range given.
Example:
If I was looking up a weekly salary in Manitoba for 3195, we would find the range 3184-3216.
What the table will do is use the mean value for this range (which would be 3200) and calculate the income tax based on that amount, not 3195.
Code:
=< >
3184 - 3216
3216 - 3248
3248 - 3280What I would like to do is remove the need to look up any of the tables. Before choosing the calculate the tax for the amount given, the user will have two options of "Use Tax Table" and "Use CRA Formulas". If the user chooses the first option, I would like the user to enter the number as 3195 and the result will be for the mean value of 3200.
These tables are provided as pdf files (one for each province/territory), and each includes the federal and well as its respective tables for each of the pay schedules.
IF it were possible to extract the information from the PDFs and get the proper mean value, then I would ask for directions to do that along with coding to find the mean and so on. The problem with this is when I decide to include Quebec in the calculations, the PDF file is ... well different.
I haven't been able to save it as a text file and have all of the tables appear as they should. So I think for now, if I have to go into each PDF and copy the first two colums out and add them to Excel, I would be more than willing to do so. I just need to find out what to do after that.
The point of this exercise is that our payroll class needs to look up the tax amounts on the table with the claim codes with each assignment, which they should learn how to do. As an instructor, working on each class assignment, I see the formulas as the most efficient way to spend my time (less time spent looking up the tables, the more assignments I can give my class). Enter the income, and out comes the tax amounts. The problem is my current formula results, although accurate, will not match the class's table amounts... And this is where the above coding would be nice to have.
Thank you to all that read this.
Thank you very much to those who will attempt to solve the puzzle.
-- g
