Need help with formula please

[laurencp72]

Hi all! I need help with a formula/function for calculating payroll taxes.

What I have is this:


Sheet 1

A
1 756.00
2 30.00


Sheet 2

A B
1 720 - 740 28
2 740 - 760 30
3 760 - 780 32


Sheet 1 -
Row 1 will change each week
Row 2 depends on the amount in Row 1

If A1 (Sheet 1) is in a certain range, it needs to match the number on Sheet 2. Kind of hard to explain, but I hope someone understands this.

Thanks in advance for any help!

Lauren

 

[bryonwoods30]

in the values you give in column a on sheet b, your min is 720 and your max is 780.

A B
1 720 - 740 28
2 740 - 760 30
3 760 - 780 32

what is the smallest and biggest numbers in column a?

 

[MrKowz]

Try a VLOOKUP:

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">756</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">30</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A2</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(A1,Sheet2!A1:B3,2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">720</TD><TD style="TEXT-ALIGN: right">28</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">740</TD><TD style="TEXT-ALIGN: right">30</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">760</TD><TD style="TEXT-ALIGN: right">32</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

Note: In Sheet1, column A is 720, 740, 760... not "720 - 740", "740 - 760" etc...

 

[laurencp72]

The VLOOKUP could possibly work, but I am really not skilled when it comes to alot of functions and formulas. I will fool around with that one; maybe I can figure it out.

 

[laurencp72]

Awesome! VLOOKUP worked! Thank you so much!

 

[T. Valko]

The VLOOKUP could possibly work, but I am really not skilled when it comes to alot of functions and formulas. I will fool around with that one; maybe I can figure it out.

A B
1 720 - 740 28
2 740 - 760 30
3 760 - 780 32

One problem is that your ranges overlap.

Typically, the ranges might be something like this:

720 to 739
740 to 759
760 to 779

Or

720 to 740
741 to 760
761 to 780

When you setup the table you only want the lower boundary of the range.

720...28
740...30
760...32

Sheet1 A1 = 756

=LOOKUP(A1,Sheet2!A1:B3)

Note that if A1 is less than the min value in the table (in this case 720) the formula will return an error.