Formula that takes a range of values into consideration
 

In cell D5 i have a figure that can varry in cell G5 i need a formula that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.

Formula that takes a range of values into consideration
 

Look in the help index for VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a formula that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.

Formula that takes a range of values into consideration
 

You want to use =IF((D5)<37106,(D5)*.2005,IF((D5)<40970,(D5)*.2415 ,
.............. so on and so on)






"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a formula that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.

Formula that takes a range of values into consideration
 

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a formula that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.

Formula that takes a range of values into consideration
 

something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
--
A.


"T. Valko" wrote:

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a formula that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.


.

Formula that takes a range of values into consideration
 

You didn't say exactly how you were going to use this table so...How do you
intend to use this table?

What's in D5?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
--
A.


"T. Valko" wrote:

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a formula
that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.


.

Formula that takes a range of values into consideration
 

I am calculating net income. in D5 is where the gross goes in the ranges are
the various tax brackets and the percentages listed is the tax rate related
to that income level.
--
A.


"T. Valko" wrote:

You didn't say exactly how you were going to use this table so...How do you
intend to use this table?

What's in D5?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
--
A.


"T. Valko" wrote:

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a formula
that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.


.



.

Formula that takes a range of values into consideration
 

Ok, if D5 = 66,000

Based on the table I posted, this formula:

=LOOKUP(D5,A1:B8)

Will return 0.3298, formatted as Percentage to 2 decimal places, 32.98%.

That is the correct result based on your explantion of the intervals:

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%

So, what's the problem?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
I am calculating net income. in D5 is where the gross goes in the ranges
are
the various tax brackets and the percentages listed is the tax rate
related
to that income level.
--
A.


"T. Valko" wrote:

You didn't say exactly how you were going to use this table so...How do
you
intend to use this table?

What's in D5?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
--
A.


"T. Valko" wrote:

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a
formula
that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.


.



.

Formula that takes a range of values into consideration
 

k to verify i've done 70,000 at 32.98% income tax rate i get a net pay of
$46914 but the result from the calculation populates $67.02 in G5. Now I have
edited the percentage multiply D5 by 67.02 to show net income.
--
A.


"T. Valko" wrote:

Ok, if D5 = 66,000

Based on the table I posted, this formula:

=LOOKUP(D5,A1:B8)

Will return 0.3298, formatted as Percentage to 2 decimal places, 32.98%.

That is the correct result based on your explantion of the intervals:

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%

So, what's the problem?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
I am calculating net income. in D5 is where the gross goes in the ranges
are
the various tax brackets and the percentages listed is the tax rate
related
to that income level.
--
A.


"T. Valko" wrote:

You didn't say exactly how you were going to use this table so...How do
you
intend to use this table?

What's in D5?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
--
A.


"T. Valko" wrote:

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a
formula
that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.


.



.



.

Formula that takes a range of values into consideration
 

Still not clear what you're trying to do.

To get a result of 46,194...

D5 = 70,000

=D5*(1-LOOKUP(D5,A1:B8))

I have no idea what $67.02 means or how you're arriving at that result.

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
k to verify i've done 70,000 at 32.98% income tax rate i get a net pay of
$46914 but the result from the calculation populates $67.02 in G5. Now I
have
edited the percentage multiply D5 by 67.02 to show net income.
--
A.


"T. Valko" wrote:

Ok, if D5 = 66,000

Based on the table I posted, this formula:

=LOOKUP(D5,A1:B8)

Will return 0.3298, formatted as Percentage to 2 decimal places, 32.98%.

That is the correct result based on your explantion of the intervals:

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%

So, what's the problem?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
I am calculating net income. in D5 is where the gross goes in the ranges
are
the various tax brackets and the percentages listed is the tax rate
related
to that income level.
--
A.


"T. Valko" wrote:

You didn't say exactly how you were going to use this table so...How
do
you
intend to use this table?

What's in D5?

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
--
A.


"T. Valko" wrote:

Create a 2 column table like this:

0...20.05%
37106.01...24.15%
40970.01...31.15%
65345.01...32.98%
74214.01...35.39%
76986.01...39.41%
81941.01...43.41%
127021.01...46.21%

Assume that table is in the range A1:B8.

D5 = some number

Then:

=LOOKUP(D5,A1:B8)

--
Biff
Microsoft Excel MVP


"Andmor" wrote in message
...
In cell D5 i have a figure that can varry in cell G5 i need a
formula
that
will do calculations depending on a range of values.

first $37,106 20.05%
over $37,106 up to $40,970 24.15%
over $40,970 up to $65,345 31.15%
over $65,345 up to $74,214 32.98%
over $74,214 up to $76,986 35.39%
over $76,986 up to $81,941 39.41%
over $81,941 up to $127,021 43.41%
over $127,021 46.41%


--
A.


.



.



.