Banding data
 

I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A B
55 2%
66 3%
33 1%
23 1%
1 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?

Banding data
 

in Cell B1 put this formula and drag it down

format cell to percentage ( select col B - Go to format | cells |
number category: percentage | ok )

=INT((A1-1)/20)/1*0.01


On Feb 11, 11:27*pm, Steve wrote:
I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A * * * * * * * * * *B
55 * * * * * * * * * 2%
66 * * * * * * * * * 3%
33 * * * * * * * * * 1%
23 * * * * * * * * * 1%
1 * * * * * * * * * * 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?

Banding data
 

One way:

=IF(COUNT(A1),MAX(0,(CEILING(A1,20)/20-1)/100),"")

Format as Percentage 0 decimal places.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A B
55 2%
66 3%
33 1%
23 1%
1 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?

Banding data
 

=INT((A1-1)/20)/1*0.01

An empty cell or a cell containing numeric 0 will return an incorrect
result.


--
Biff
Microsoft Excel MVP


"muddan madhu" wrote in message
...
in Cell B1 put this formula and drag it down

format cell to percentage ( select col B - Go to format | cells |
number category: percentage | ok )

=INT((A1-1)/20)/1*0.01


On Feb 11, 11:27 pm, Steve wrote:
I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A B
55 2%
66 3%
33 1%
23 1%
1 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?

Banding data
 

sorry for that

may be this one

=IF(ISBLANK(A1),"",IF(A10,INT((A1-1)/20)/1*0.01,0))


On Feb 11, 11:56*pm, "T. Valko" wrote:
=INT((A1-1)/20)/1*0.01

An empty cell or a cell containing numeric 0 will return an incorrect
result.

--
Biff
Microsoft Excel MVP

"muddan madhu" wrote in message

...
in Cell B1 put this formula and drag it down

format cell to percentage ( select col B - Go to format | cells |
number category: percentage | ok )

=INT((A1-1)/20)/1*0.01

On Feb 11, 11:27 pm, Steve wrote:

I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A B
55 2%
66 3%
33 1%
23 1%
1 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?

Banding data
 

Thank you to everyone for there help, both formulas work perfect.

Thanks again.


On 11 Feb, 19:24, muddan madhu wrote:
sorry for that

may be this one

=IF(ISBLANK(A1),"",IF(A10,INT((A1-1)/20)/1*0.01,0))

On Feb 11, 11:56*pm, "T. Valko" wrote:



=INT((A1-1)/20)/1*0.01

An empty cell or a cell containing numeric 0 will return an incorrect
result.

--
Biff
Microsoft Excel MVP

"muddan madhu" wrote in message

...
in Cell B1 put this formula and drag it down

format cell to percentage ( select col B - Go to format | cells |
number category: percentage | ok )

=INT((A1-1)/20)/1*0.01

On Feb 11, 11:27 pm, Steve wrote:

I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A B
55 2%
66 3%
33 1%
23 1%
1 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?- Hide quoted text -

- Show quoted text -

Banding data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thank you to everyone for there help, both formulas work perfect.

Thanks again.


On 11 Feb, 19:24, muddan madhu wrote:
sorry for that

may be this one

=IF(ISBLANK(A1),"",IF(A10,INT((A1-1)/20)/1*0.01,0))

On Feb 11, 11:56 pm, "T. Valko" wrote:



=INT((A1-1)/20)/1*0.01

An empty cell or a cell containing numeric 0 will return an incorrect
result.

--
Biff
Microsoft Excel MVP

"muddan madhu" wrote in message

...
in Cell B1 put this formula and drag it down

format cell to percentage ( select col B - Go to format | cells |
number category: percentage | ok )

=INT((A1-1)/20)/1*0.01

On Feb 11, 11:27 pm, Steve wrote:

I am complete novice when it comes to these things, I have a set of
data, I want to band the data to display percentages besides it. So
when the data in column A is between 0-20 its 0% in B, 21-40 its 1%,
41-60 its 2%, 61-80 its 3% and 81-100 its 4%

A B
55 2%
66 3%
33 1%
23 1%
1 0%

I want the B column to work itself out, I have tried if else
statements without much look and a lot of error messeages, can anyone
help please?- Hide quoted text -

- Show quoted text -

Banding data
 

Hello,

=LOOKUP(-A1,{-1E+307,-60,-40,-20},{0.03,0.02,0.01,0})

I hope you have to change 60 to 59 some day, for example :-)

Regards,
Bernd