Excel Formula Help..! 3

[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 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!

 

[KenWright]

One way:-

Range of values for your percentages

0 0.100
100 0.070
200 0.065
300 0.060
400 0.055
500 0.050
600 0.045
700 0.040
800 0.035
900 0.030
1000 0.025

I'll assume these are in the range F1:G11. I'll further assume your values to be factored are in A2:A30.

In B2 put the following and then copy down

=IF(OR(MOD(B2,INT(B2))>0.95,MOD(B2,INT(B2))=0),ROUNDUP(A2*(1+VLOOKUP(A2,$G$1:$H$11,2)),0)+0.95,ROUNDUP(A2*(1+VLOOKUP(A2,$G$1:$H$11,2)),0)-0.05)

Sure there must be a shorter one but it's getting late over here.

Regards
Ken............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi AM123,

I'm with Ken, there must be a shorter way

It depends on what you want to do with values like 12.7, which works out at 13.97 - do you want to round it down to 13.95 or up to 14.95?

Using Ken's example lookup table (in G1:H11 to fit with the formula), I think this will do it ..

[blue][tt] =INT(A2*(1+VLOOKUP(A2,$G$1:$H$11,2)))+0.95[/tt][/blue]

If you want 12.7 (as per the example above) to be 14.95 then try this instead ..

[blue][tt] =INT(CEILING(A2*(1+VLOOKUP(A2,$G$1:$H$11,2)),0.05))+0.95[/tt][/blue]


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

LOL - ignore mine anyway, I must have had checksum cells and accidentally referenced them.

That having been said, I started off with something similar to that Tony, but when I tested against a bunch of numbers I found that any value that generated a number ending in 0.96 0.97 0.98 0.99 did not get 'rounded up' as per the OPs note. That's the part I'm having a hard time doing other than with a long winded formula. If the OP is happy with rounding down these values then it works a treat.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

OK, I'm still stuck with a long formula:-

Changing reference to assume source data is in Col D as per OPs note, but leaving lookup table as previously stated:-

=INT(D2*(1+VLOOKUP(D2,$H$1:$I$11,2)))+0.95+(MOD(D2*(1+VLOOKUP(D2,$H$1:$I$11,2)),INT(D2*(1+VLOOKUP(D2,$H$1:$I$11,2))))>0.95)

If you are happy to round 0.96/0.97/0.98/0.99 values down to 0.95 the go with the formula Tony has given.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi Ken,

What value does my second formula fail with??

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

Doh!!! - I just saw the 12 becoming 14 and hadn't realised you were referring to an original number before the addition of the percentage. Apologies - My bad and a star from me

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Thanks Ken - a star from you is always an honour

I did think I had it all covered but I'm never 100% certain; it's so easy to miss a situation with this kind of formula.



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[AM123]

I want to thank-you guys for this.. Amazing! I am really impressed and never would have been able to do this without your help. I am started to enter this and everything is looking great!