| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . . . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Months while taking into consideration days | Excel Worksheet Functions | |||
| IF formula using a range of values in a cell | Excel Worksheet Functions | |||
| How to define range of values in a formula? | Excel Worksheet Functions | |||
| formula for range values question | Excel Discussion (Misc queries) | |||
| MSOffice Excel 2003-Validation Listbox takes only 47 values | Excel Worksheet Functions | |||
Linear Mode
