Tables & if greater than / less than formulas

[atran008]

Hi all,

I manage a team of payroll with different salaries. I am looking for a solution where I can input the salary figure in a cell which it is matched against the conditions in the table below to output the correct Levy amount.

Does anybody know of an efficient way to do this? Perhaps organise the table differently in excel? Thanks in advance.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Salary[/TD]
[TD]Levy[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD] ≤ 2,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 93"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]$0.5[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD] > 2,000 to 3,500[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 93"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]$1[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD] > 3,500 to 5,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 93"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]$1.5[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD] > 5,000 to 7,500[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 93"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]$2[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD] > 7,500[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 93"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]$3[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[mail2master]

Put your Salary in Column A. In Column B write down this formula:

=IF(A1<=2000,0.5,IF(A1<=3500,1,IF(A1<=5000,1.5,IF(A1<=7500,2,3))))
It would give you the desired Levy in column B. In case Levy is variable, you can link a cell instead of giving a constant in formula.

Is it efficient way of doing it ? I don't know... Does it solve your problem ? Well, Yes...

 

[jtakw]

Hi,

Set up your Table like my sample A2:B6, then whenever there's a change, just change them within the table:

Book1
	A	B	C	D	E
1	Salary	Levy		Employee Salary	Levy to apply
2	0	0.50		1000	0.5
3	2001	1.00		2000	0.5
4	3501	1.50		2050	1
5	5001	2.00		3500	1
6	7501	3.00		3510	1.5
7				5000	1.5
8				6000	2
9				7500	2
10				9000	3
Sheet516

Cell Formulas
Range	Formula
E2	=LOOKUP(D2,A$2:A$6,B$2:B$6)