# 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

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
>

T. Valko
Guest
Posts: n/a

 27th Jan 2007

=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
>

=?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
>
>

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

=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
>>

>
>

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

>
>

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

=?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
> >
> >

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

>>
>>

>
>

=?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
> >>
> >>

>
>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 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 OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Jason Microsoft Excel Misc 2 5th Apr 2010 06:11 PM Sisilla Microsoft Excel Programming 12 6th Oct 2006 08:04 PM Geezer Microsoft Excel Programming 5 18th Mar 2005 12:45 PM Eric Hedden Microsoft Excel Misc 8 11th Mar 2004 09:02 PM eksecretary Microsoft Excel Misc 2 16th Feb 2004 03:18 PM

Features

Newsgroups

All times are GMT +1. The time now is 08:18 AM.