Greater than Less than

K

kicknchickn

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
 
S

Sandy Mann

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 address removed)
(e-mail address removed) with @tiscali.co.uk
 
T

T. Valko

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
 
G

Guest

Try this:

With a value in A1

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

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

XL2002, WinXP
 
S

Sandy Mann

Sandy Mann said:
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 address removed)
(e-mail address removed) with @tiscali.co.uk


Sandy Mann said:
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 address removed)
(e-mail address removed) with @tiscali.co.uk


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

T. Valko

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

Think the final divisor should be 10:

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

Biff

Sandy Mann said:
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 address removed)
(e-mail address removed) with @tiscali.co.uk


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

T. Valko

An empty cell or 0 returns 0.04

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

Biff

Ron Coderre said:
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:
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
 
G

Guest

Ooops! I left a piece off...

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

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

XL2002, WinXP


Ron Coderre said:
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:
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
 
S

Sandy Mann

T. Valko said:
Think the final divisor should be 10:

No that formula doesn't work either but my second attempt does - depending

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 address removed)
(e-mail address removed) with @tiscali.co.uk


T. Valko said:
=B8*(FLOOR(B8,10)/10+5)/100

Think the final divisor should be 10:

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

Biff

Sandy Mann said:
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 address removed)
(e-mail address removed) with @tiscali.co.uk


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
 
G

Guest

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 said:
An empty cell or 0 returns 0.04

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

Biff

Ron Coderre said:
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:
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
 
S

Sandy Mann

T. Valko said:
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()

--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


T. Valko said:
An empty cell or 0 returns 0.04

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

Biff

Ron Coderre said:
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:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top