Desperately in need of help

[Cdn Bookkeeper]

Hi,
First, thank you for allowing me to join this group.
I have a problem that I'm sure will be easy for me but is waaaay out of my league.
I need a formula that will give a certain result depending on a number of inputs.
(IF( B3*52) is less than t, then 1/52 or more than t but less than q, then 2/52 or more than q but less than r, then 3/52 etc.

t=400 1=100
q=600 2=200
s=800 3=300
so If B3 x 52 is 1000
then if 1000<400,100/52 or if 1000<600>800,200/52 or if 1000<800>1001,300/52 and on

Do you have any idea how I might write this formula so that it actually works?
Thanks in advance

 

[Cdn Bookkeeper]

Hello Cdn Bookkeeper

I do not recognize some of the information that you provided.

Please step back and provide the reference source for your input data.
" 208000 annual gross wages. TD 1 exemption for this level of earnings is -13767.00. "

What TD1 or TD1-WS did you use for that calculation?

For reference:

CANADA REVENUE FEDERAL INCOME TAX BRACKETS
TD1 IS THE SAME AS BASE TAX
WAGES OVER	NOT TO EXCEED	BASE TAX	PLUS % OF	WAGES OVER
0.00​	53359.00​	0.00​	15.0%​	0.00​
53359.00​	106717.00​	(2935.00)​	20.5%​	0.00​
106717.00​	165430.00​	(8804.00)​	26.0%​	0.00​
165430.00​	235675.00​	(13767.00)​	29.0%​	0.00​
235675.00​	9999999.99​	(23194.00)​	33.0%​	0.00​
FOR WEEKLY LIMITS DIVIDE BY 52
0.00​	1026.13​	0.00​	15.0%​	0.00​
1026.13​	2052.25​	(56.44)​	20.5%​	0.00​
2052.25​	3181.35​	(169.31)​	26.0%​	0.00​
3181.35​	4532.21​	(264.75)​	29.0%​	0.00​
4532.21​	192307.69​	(446.04)​	33.0%​	0.00​

 

[Dave Patton]

You state "TD1 IS THE SAME AS BASE TAX".

The TD1 is the form for Personal Tax Credits; it is not the same as the tax brackets.

I quickly did a calculation of the Federal Tax; please compare to your calculations.

Income Tax 2023.xlsm
	A	B	C
1	Information for 2023
2		Income	Federal Non_Refundable Tax Credits
3	Input	208,000.00	14,103.69
4	Tax Payable		44,437.37
5	Medical
1a

Cell Formulas
Range	Formula
C4	C4	=Fed_Tax2023(B3,C3)

 

[Alex Blakenburg]

I think @Dave Patton might have a better understanding of what the calculation is.

Your examples seem to ignore the plus %, and I think you are misunderstanding how the vlookup is being used, so here is another example.

20230303 VLookup Rate Table Cdn Bookkeeper.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Emp ID	Hrly Rate	Hrs	Weekly Pay	Use Wkly Table	Use Annual Table			CANADA REVENUE FEDERAL INCOME TAX BRACKETS
2	1	35	40	1400	-56.44	-56.44			TD1 IS THE SAME AS BASE TAX
3	2	100	40	4000	-264.75	-264.75			WAGES OVER	NOT TO EXCEED	BASE TAX	PLUS % OF	WAGES OVER
4									0	53359	0	15.00%	0
5									53359	106717	-2935	20.50%	0
6									106717	165430	-8804	26.00%	0
7									165430	235675	-13767	29.00%	0
8									235675	9999999.99	-23194	33.00%	0
9
10									FOR WEEKLY LIMITS DIVIDE BY 52
11
12									0	1026.13	0	15.00%	0
13									1026.13	2052.25	-56.44	20.50%	0
14									2052.25	3181.35	-169.31	26.00%	0
15									3181.35	4532.21	-264.75	29.00%	0
16									4532.21	192307.69	-446.04	33.00%	0
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=B2*C2
E2:E3	E2	=VLOOKUP($D2,$I$12:$M$16,3,TRUE)
F2:F3	F2	=ROUND(VLOOKUP($D2*52,$I$4:$M$8,3,TRUE)/52,2)