PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Discussion > Greater than Less than

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Greater than Less than

 
 
kicknchickn@gmail.com
Guest
Posts: n/a
 
      26th Jan 2007
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
 
Reply With Quote
 
 
 
 
Sandy Mann
Guest
Posts: n/a
 
      27th Jan 2007
With your 15 in B8 try:

=B8*(FLOOR(B8,10)/10+5)/100

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
 
Reply With Quote
 
 
 
 
T. Valko
Guest
Posts: n/a
 
      27th Jan 2007
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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
 
Reply With Quote
 
=?Utf-8?B?Um9uIENvZGVycmU=?=
Guest
Posts: n/a
 
      27th Jan 2007
Try this:

With a value in A1

B1: =CEILING(A1,10)*0.001+0.04

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"(E-Mail Removed)" wrote:

> 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
>
>
 
Reply With Quote
 
Sandy Mann
Guest
Posts: n/a
 
      27th Jan 2007
"Sandy Mann" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Sandy Mann" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> With your 15 in B8 try:
>
> =B8*(FLOOR(B8,10)/10+5)/100
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> (E-Mail Removed) with @tiscali.co.uk
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
>>
>
>
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      27th Jan 2007
> =B8*(FLOOR(B8,10)/10+5)/100

Think the final divisor should be 10:

=B8*(FLOOR(B8,10)/10+5)/10

Biff

"Sandy Mann" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> With your 15 in B8 try:
>
> =B8*(FLOOR(B8,10)/10+5)/100
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> (E-Mail Removed) with @tiscali.co.uk
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
>>
>
>
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      27th Jan 2007
An empty cell or 0 returns 0.04

=CEILING(A1,10)*0.001+(A1<>"")*0.04

Biff

"Ron Coderre" <(E-Mail Removed)> wrote in message
news:B017CB0B-93D8-4169-BE8B-(E-Mail Removed)...
> Try this:
>
> With a value in A1
>
> B1: =CEILING(A1,10)*0.001+0.04
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "(E-Mail Removed)" wrote:
>
>> 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
>>
>>
 
Reply With Quote
 
=?Utf-8?B?Um9uIENvZGVycmU=?=
Guest
Posts: n/a
 
      27th Jan 2007
Ooops! I left a piece off...

=MAX(CEILING(A1,10)*0.001+0.04,0.05)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

> Try this:
>
> With a value in A1
>
> B1: =CEILING(A1,10)*0.001+0.04
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >
 
Reply With Quote
 
Sandy Mann
Guest
Posts: n/a
 
      27th Jan 2007
T. Valko" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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?

--
Regards,

Sandy

In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"T. Valko" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>> =B8*(FLOOR(B8,10)/10+5)/100
>
> Think the final divisor should be 10:
>
> =B8*(FLOOR(B8,10)/10+5)/10
>
> Biff
>
> "Sandy Mann" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> With your 15 in B8 try:
>>
>> =B8*(FLOOR(B8,10)/10+5)/100
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> (E-Mail Removed)
>> (E-Mail Removed) with @tiscali.co.uk
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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
>>>
>>
>>
>
>
 
Reply With Quote
 
=?Utf-8?B?Um9uIENvZGVycmU=?=
Guest
Posts: n/a
 
      27th Jan 2007
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


"T. Valko" wrote:

> An empty cell or 0 returns 0.04
>
> =CEILING(A1,10)*0.001+(A1<>"")*0.04
>
> Biff
>
> "Ron Coderre" <(E-Mail Removed)> wrote in message
> news:B017CB0B-93D8-4169-BE8B-(E-Mail Removed)...
> > Try this:
> >
> > With a value in A1
> >
> > B1: =CEILING(A1,10)*0.001+0.04
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> >> 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
> >>
> >>
>
>
>
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Excel formula flicker / erase formula / won't close | SEND TO - E-MAIL »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
If formula: if A1 is greater than B1 and A1 is greater than zero.. Jason Microsoft Excel Misc 2 5th Apr 2010 06:11 PM
CountIf Greater Than/Find Greater Than Sisilla Microsoft Excel Programming 12 6th Oct 2006 08:04 PM
So many greater than and less than values to calculate Geezer Microsoft Excel Programming 5 18th Mar 2005 12:45 PM
Greater than or less than Eric Hedden Microsoft Excel Misc 8 11th Mar 2004 09:02 PM
less than greater than formula required eksecretary Microsoft Excel Misc 2 16th Feb 2004 03:18 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 08:18 AM.
Powered by vBulletin®. Copyright ©2000 - 2013, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top