lesser than, greater than

[bigproblem]

i have a table -

column a column b
$0 - $1,000,000 0.00%
$1,000,001 - $5,000,000 2.23%
$5,000,001 - $9,000,000 3.11%
$9,000,001 - $12,000,000 4.49%
$12,000,001 and above 5.65%

if e18 is more than 1,000,001 and less than 5,000,000 i want to mulitple e18
by 2.23. if e18 is more than 5,000,001 and less than 9,000,000 i want to
mulitple e18 by 3.11. if e18 is more than 9,000,001 and less than 12,000,000
i want to mulitple e18 by 4.49. same if e18 is above 12,000,0001.

How do i do that ?

 

[JoeU2004]

if e18 is more than 1,000,001 and less than 5,000,000 i want
to mulitple e18 by 2.23.

First, I assume you mean "more than 1,000,000" or "not less than 1,000,001".
That is the typical interpretation of such tables.

Second, I assume you mean "multiply by 2.23%", or at least the number you
have in column B.

Start by reorganizing the table so that column A is simply the lower limit.
Then:

=E18*vlookup(E18,$A$1:$B$5,2)

If you truly want "more than 1,000,001" (e.g. 1,000,001.01 and more), change
the lower limit to 1000001.01, and use VLOOKUP(ROUND(E18,2),...).


----- original message -----

 

[Per Jessen]

Another option entered as one line:

=IF(E18<=1000000,0,IF(E18<=5000000,E18*2.23%,IF(E18<=9000000,E18*3.11%,IF(E18<=12000000,E18*4.49%,E18*5.65%))))


Regards,
Per

 

[bigproblem]

depending on where e18 falls in the range i want to multiple it (e18) by
what is in column c

column a column b column c
0 1,000,000 0.00%
1,000,001 3,000,000 4.25%
3,000,001 5,000,000 5.15%
5,000,001 7,000,000 5.40%

 

[Harald Staff]

Yet another just for the fun of it:

=E18*(0,0223*(E18>1000000)+0,0088*(E18>5000000)+0,0179*(E18>9000000)+0,0075*(E18>12000000))

I'd do what JoeU2004 suggests, VLOOKUP, by far the easiest to understand and
maintain when values change in a year or so.

But we are doing percentage of the whole amount: May it be that the result
really should be 0 for the first million, then 2.23 of only anything between
1 and 5 million, and then 3.11 only of anything between 5 and 9 million, and
so on, as in progressive tax?

Best wishes Harald

 

[Harald Staff]

Column 2 is now column 3:
=E18*vlookup(E18,$A$1:$B$5,3)

HTH. Best wishes Harald

 

[JoeU2004]

depending on where e18 falls in the range i want to multiple it
(e18) by what is in column c

Similar to before, namely:

=E18*vlookup(E18,$A$1:$C$5,3)

I am assuming you inadvertently omitted the line for "7,000,001 and
above" -- row 5.

But I wonder....

1. What percentage should apply to 3,000,000.01?

2. Are you correct about your interpretation of the table? Instead of
multiplying all of E18 by a single percentage, should you apply 0% to the
first 1,000,000, 4.25% to the amount over 1,000,000 up to and including
3,000,000, etc?

For example, for 2,000,000 in E18, should the result be 85,000
(2,000,000 * 4.25%), or should the result be 42,500 -- 1,000,000 * 0% +
(2,000,000 - 1,000,000) * 4.25%?


----- original message -----