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

 

[awoohaw]

what cell do you want the calculation in? What happens if B3*52 > t?

 

[Cdn Bookkeeper]

what cell do you want the calculation in? What happens if B3*52 >

I, ideally want it to calculate the TD1 exemption amount based on weekly paycheques.
If a weekly cheque is pay rate x hours x 52 weeks ie 100 x 40 x 52 = 208000 annual gross wages.
TD 1 exemption for this level of earnings is -13767.00. Divide by 52 pay periods to get the TD1 exemption per pay period. -264.75

There is a scale
0 to 53359 = 0
53359 to 106717 = -2935
106717 to 165430 =-8804
165430 to 235675 = -13767
235675 to 999999 = -23194

Any idea how I can do it?

 

[Alex Blakenburg]

I don't fully follow your calculation explanation but if you have a rate table like you do VLookup or XLookup is the way to go.
The bellow will give you the basic lookup which will need to be modified for the rest of your calculation which I suspect is just rate*hrs*52, use that figure for the lookup, then divide the result by 52 to get the weekly figure.

Book2
	A	B	C	D	E	F	G
1	From	To	Exemption		Input Value	Result
2	0	53,359	0		55,000	-2,935
3	53,359	106,717	-2,935
4	106,717	165,430	-8,804
5	165,430	235,675	-13,767
6	235,675	999,999	-23,194
7
Sheet1

Cell Formulas
Range	Formula
F2	F2	=VLOOKUP(E2,$A$2:$C$6,3,TRUE)

 

[Cdn Bookkeeper]

I don't fully follow your calculation explanation but if you have a rate table like you do VLookup or XLookup is the way to go.
The bellow will give you the basic lookup which will need to be modified for the rest of your calculation which I suspect is just rate*hrs*52, use that figure for the lookup, then divide the result by 52 to get the weekly figure.

Book2
	A	B	C	D	E	F	G
1	From	To	Exemption		Input Value	Result
2	0	53,359	0		55,000	-2,935
3	53,359	106,717	-2,935
4	106,717	165,430	-8,804
5	165,430	235,675	-13,767
6	235,675	999,999	-23,194
7
Sheet1

Cell Formulas
Range	Formula
F2	F2	=VLOOKUP(E2,$A$2:$C$6,3,TRUE)

Here is what I want to do:

I don't fully follow your calculation explanation but if you have a rate table like you do VLookup or XLookup is the way to go.
The bellow will give you the basic lookup which will need to be modified for the rest of your calculation which I suspect is just rate*hrs*52, use that figure for the lookup, then divide the result by 52 to get the weekly figure.

Book2
	A	B	C	D	E	F	G
1	From	To	Exemption		Input Value	Result
2	0	53,359	0		55,000	-2,935
3	53,359	106,717	-2,935
4	106,717	165,430	-8,804
5	165,430	235,675	-13,767
6	235,675	999,999	-23,194
7
Sheet1

Cell Formulas
Range	Formula
F2	F2	=VLOOKUP(E2,$A$2:$C$6,3,TRUE)

I hope this makes it clearer. I would like the calculation result to appear in one box. I am trying to create a payroll workbook for a client to record an employee's weekly wages and calculate the TD1 Exemption applicable based on the weekly salary x 52 weeks. As this changes from week to week with the hours worked, the calculation may be different each week when that wage is multiplied by 52. Please look at what I have so far.

TD1
	IF(OR(C64<C66,C64>B67<C67, C62>B66<C66, C62>B67>C67, C62>B68>C68),D66,D67, D66, D67, D68, D69, D70
	THIS ONE SAYS TOO MANY FUNCTIONS
	IF(OR(C64=<B66, >B67<C67, >B68<C68, >B69>C69, >B70>C70),D66:D70
	ERROR SOMEWHERE IN THIS FORMULA
		WEEKLY WAGE
		4,150.00
	FROM	TO	EXEMPTION
1​		1,026.13	0
2​	1,026.14	2,052.25	(56.44)
3​	2,052.26	3,181.35	(169.31)
4​	3,181.36	4,532.21	(264.75)
5​	4,532.22	19,230.75	(446.04)
	ANSWER: (264.75)

 

[Cdn Bookkeeper]

I don't fully follow your calculation explanation but if you have a rate table like you do VLookup or XLookup is the way to go.
The bellow will give you the basic lookup which will need to be modified for the rest of your calculation which I suspect is just rate*hrs*52, use that figure for the lookup, then divide the result by 52 to get the weekly figure.

Book2
	A	B	C	D	E	F	G
1	From	To	Exemption		Input Value	Result
2	0	53,359	0		55,000	-2,935
3	53,359	106,717	-2,935
4	106,717	165,430	-8,804
5	165,430	235,675	-13,767
6	235,675	999,999	-23,194
7
Sheet1

Cell Formulas
Range	Formula
F2	F2	=VLOOKUP(E2,$A$2:$C$6,3,TRUE)

I forgot part of the table.

IDEALLY, I WANT THE FORMULA RESULT TO BE IN ONE CELL RATHER THAN A TABLE
AS IT IS PART OF A LARGER CALCULATION
IF C62 IS LESS THAN C64 THEN 0.00
IF C62 IS MORE THAN B65 BUT LESS THAN C65 THEN -56.44
IF C62 IS MORE THAN B66 BUT LESS THANK C66 THEN -169.31
IF C62 IS MORE THAN B67 BUT LESS THAN C67 THEN -264.75
IF C62 IS MORE THAN B68 BUT LESS THAN C68 THEN -446.04

 

[Cdn Bookkeeper]

Just for information, I am using Excel 2007

 

[Joe4]

Just for information, I am using Excel 2007

Then please update your profile to accurately reflect that:

 

[Dave Patton]

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?

 

[Cdn Bookkeeper]

Then please update your profile to accurately reflect that:
View attachment 86708

Done. Sorry about that. Client has 2021