Banding data

[Steve]

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?

 

[muddan madhu]

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

 

[T. Valko]

One way:

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

Format as Percentage 0 decimal places.

Copy down as needed.

 

[T. Valko]

=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


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

 

[muddan madhu]

sorry for that

may be this one

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

 

[Steve]

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

Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Thanks again.

 

[Bernd P]

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