Formula that takes a range of values into consideration

  • Thread starter Thread starter Andmor
  • Start date Start date
A

Andmor

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%
 
You want to use =IF((D5)<37106,(D5)*.2005,IF((D5)<40970,(D5)*.2415,
.............. so on and so on)
 
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)
 
something is not adding up, G5 now equals 32.98 when it should equal
somewhere around $46,900
 
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?
 
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.
 
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%
Click to expand...

So, what's the problem?
 
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.
 
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.
 
Back
Top