Excel Formula depending on number in cell..

A

AM123

Hello everyone!

I have a question that I thought some of you could answer:

I have a number in column D and based on if it is $0-$100 I want i
multiplied by 0.10 and then rounded up to .95.

So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95

Now this is where it gets tricky..

If it is between 100.01 to 200.00 + 7% and then rounded up to a .95.

So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95
$138.95.

I need to do this for a range of numbers like 200.00 to 300.00 an
300.00 to 500.00 etc, etc..

Could someone point me in the right direction?

I appreciate all of your help! :
 
R

Ron Rosenfeld

Hello everyone!

I have a question that I thought some of you could answer:

I have a number in column D and based on if it is $0-$100 I want it
multiplied by 0.10 and then rounded up to .95.

So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95

Now this is where it gets tricky..

If it is between 100.01 to 200.00 + 7% and then rounded up to a .95.

So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95 =
$138.95.

I need to do this for a range of numbers like 200.00 to 300.00 and
300.00 to 500.00 etc, etc..

Could someone point me in the right direction?

I appreciate all of your help! :)
Click to expand...

I would use a lookup table to determine the percent. For Example:

0 10%
100 7%
200 5%
300 4%
etc

Then use a formula like:

=ROUNDUP((1+VLOOKUP(A1,tbl,2))*A1+0.05,0)-0.05


--ron
 
Top