 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007Greater than Less than
I need to figure out how to multiply a number by a percentage based on the table below: 0-10 = 5% 11-20 = 6% 21-30 = 7% 31-40 = 8% Example: If the cell number is 15 it would then multiply 15*.6 which equals 0.9 in the cell next to the original value. I tried this macro: Sub Percent() If Value < 10 Then ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RC[-4]*0.05" ElseIf Value > 11 < 20 Then ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" End If With your 15 in B8 try: =B8*(FLOOR(B8,10)/10+5)/100  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk >I need to figure out how to multiply a number by a percentage based on > the table below: [quoted text clipped - 16 lines] > ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" > End If > With your 15 in B8 try: > > =B8*(FLOOR(B8,10)/10+5)/100 No Scrub that - it returns the answer in the ranges: 0-9 10-19 20 29 etc Try instead: =IF(B8=0,0,B8*(FLOOR(B8-1,10)/10+5)/100) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > With your 15 in B8 try: > [quoted text clipped - 20 lines] >> ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" >> End If > =B8*(FLOOR(B8,10)/10+5)/100 Think the final divisor should be 10: =B8*(FLOOR(B8,10)/10+5)/10 Biff > With your 15 in B8 try: > [quoted text clipped - 20 lines] >> ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" >> End If > Think the final divisor should be 10: No that formula doesn't work either but my second attempt does - depending on how you read the OP's requirements: >>> If the cell number is 15 it would then multiply 15*.6 which equals 0.9 I assumed that the 15*.6 was a typo for 15 *0.06 to give 0.9 which will be why you are saying that I should have used 10? SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk >> =B8*(FLOOR(B8,10)/10+5)/100 > [quoted text clipped - 28 lines] >>> ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" >>> End If How about a worksheet formula: =A1*LOOKUP(A1,{0,11,21,31},{0.5,0.6,0.7,0.8}) Any value >=31 the multiplier is 0.8 Biff >I need to figure out how to multiply a number by a percentage based on > the table below: [quoted text clipped - 16 lines] > ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" > End If Try this: With a value in A1 B1: =CEILING(A1,10)*0.001+0.04 Does that help? *********** Regards, Ron XL2002, WinXP > I need to figure out how to multiply a number by a percentage based on > the table below: [quoted text clipped - 16 lines] > ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" > End If An empty cell or 0 returns 0.04 =CEILING(A1,10)*0.001+(A1<>"")*0.04 Biff > Try this: > [quoted text clipped - 29 lines] >> ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" >> End If C'mon, Biff.....You've got to give me at least a couple of minutes to spot my own mistakes. How else will I learn? <vbg> *********** Regards, Ron XL2002, WinXP > An empty cell or 0 returns 0.04 > [quoted text clipped - 35 lines] > >> ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" > >> End If > An empty cell or 0 returns 0.04 Only if you are only calculating the percentage, if you are calculating the result as the OP was in his/her code: >>> ActiveCell.FormulaR1C1 = "=RC[-4]*0.05" then =A1*(CEILING(A1,10)*0.001+0.04) returns 0 with 0 or an emty cell in A1 Using CEILING() is a better option than my use of FLOOR() SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > An empty cell or 0 returns 0.04 > [quoted text clipped - 35 lines] >>> ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" >>> End If Ooops! I left a piece off... =MAX(CEILING(A1,10)*0.001+0.04,0.05) *********** Regards, Ron XL2002, WinXP > Try this: > [quoted text clipped - 29 lines] > > ActiveCell.FormulaR1C1 = "=RC[-4]*0.06" > > End If
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.