Desperate help for daughter

G

Guest

Hi

my daughter has to do a poyroll excercise for school. While I can help her
with most of it I am having trouble helping her do a VLOOKUP for the tax
table.

eg Income Tax
$0.00 0.00%
$200.00 5.00%
$500.00 10.00%
$1,000.00 15.00%
$2,000.00 25.00%
$3,000.00 35.00%
$4,000.00 45.00%

What I need to help her with is a formula that basically states if total pay
is equal to or between two figure e.g 1000 to 2000 then the the appliacble
tax % is applied.

I really hope someone can help me out on this. I think the god like status I
used to hold for her slipping!!! ;)
 
R

RagDyeR

You held for her ... OR ... she held for you ?

A1 to A7
0
200
500
1000
2000
3000
4000


B1 to B7
0
5
10
15
25
35
45


Total pay is entered in C1

Try this formula:

=VLOOKUP(C1,A1:B7,2,1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi

my daughter has to do a poyroll excercise for school. While I can help her
with most of it I am having trouble helping her do a VLOOKUP for the tax
table.

eg Income Tax
$0.00 0.00%
$200.00 5.00%
$500.00 10.00%
$1,000.00 15.00%
$2,000.00 25.00%
$3,000.00 35.00%
$4,000.00 45.00%

What I need to help her with is a formula that basically states if total pay
is equal to or between two figure e.g 1000 to 2000 then the the appliacble
tax % is applied.

I really hope someone can help me out on this. I think the god like status I
used to hold for her slipping!!! ;)
 
E

Earl Kiosterud

pjd,

If the table is in G2:H8, and the income is in A2, the tax formula would be

= VLOOKUP( A2, G2:H8, 2)

It says "look down the leftmost column of G2:H8 for what's in A2, and when
you've found it (or the last one that didn't go over it), go to column 2 of
the table for the result."
 
G

Guest

What if the salary is $1850, will it know to apply the right amount of Tax?
How will it know that it should take the %15, if the table has 1000?

Thanks

Earl Kiosterud said:
pjd,

If the table is in G2:H8, and the income is in A2, the tax formula would be

= VLOOKUP( A2, G2:H8, 2)

It says "look down the leftmost column of G2:H8 for what's in A2, and when
you've found it (or the last one that didn't go over it), go to column 2 of
the table for the result."
 
B

BenjieLop

The answer is YES ... the Vlookup table that has been presented to yo
is all that you need to solve this problem.

For your peace of mind, try entering some values yourself and see ho
it works.
What if the salary is $1850, will it know to apply the righ
amount of Tax?
How will it know that it should take the %15, if the table ha
1000?

Thank
 
G

Guest

Imagine that the list is made up of letters A, B, C,... instead of $0.00,
$200.00, $500.00,... If you put in "Apple" it would look at A and see that
this falls before Apple, then it would look at B and see that this falls
after Apple and then it would store A as the closest value that didn't exceed
"Apple." Likewise, if you had Aa, Ad, An, Aq,... it would return the value
next to An because this is the closest value to Apple that doesn't exceed it.

From the excel help on VLOOKUP it says:


"If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
largest value that is less than or equal to lookup_value. If VLOOKUP can't
find lookup_value, and range_lookup is TRUE, it uses the largest value that
is less than or equal to lookup_value. "


Now in the eqn qiven by those who responded to your question, range_lookup
was ommited and therefore its default value is true. So VLOOKUP uses the
largest value that is less than or equal to lookup_value. So $1000 is the
largest value that is less than $1850 (lookup_value), thus it is used.


pjd said:
What if the salary is $1850, will it know to apply the right amount of Tax?
How will it know that it should take the %15, if the table has 1000?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top