Excel 2002: How to classify numbers ?
 

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641

Excel 2002: How to classify numbers ?
 

In E2 copy the below formula
=LOOKUP(D2,{0,1,20,35,65,135},{"","A","B","C","D", "E"})

If this post helps click Yes
---------------
Jacob Skaria


"Mr. Low" wrote:

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641

Excel 2002: How to classify numbers ?
 

Hi Jacob,

Thanks for the first formula.

What about the formula with "greater or equal" and "less or equal" as
illustrated below:

Group defination:
0 =< A <= 20
21 =< B <= 35
36 =< C <= 65
66 =< D <=135
136 =< E

Thanks

Low


--
A36B58K641


"Jacob Skaria" wrote:

In E2 copy the below formula
=LOOKUP(D2,{0,1,20,35,65,135},{"","A","B","C","D", "E"})

If this post helps click Yes
---------------
Jacob Skaria


"Mr. Low" wrote:

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641

Excel 2002: How to classify numbers ?
 

Hi,

You can use VLOOKUP as follows:
1. Create a lookup table like this, say in L1:M5 (it looks like you will
need more in your case)
0 A
20 B
35 C
65 D
135 E

In cell E2 enter the formula

=VLOOKUP(D2,L$1:M$5,2)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr. Low" wrote:

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641

Excel 2002: How to classify numbers ?
 

Hi Shane,

I wounder if this formula could only pick up the exact match, it won't pick
up the rage of numbers in accordance with their groupings.

Any idea ?

Thanks

Low
--
A36B58K641


"Shane Devenshire" wrote:

Hi,

You can use VLOOKUP as follows:
1. Create a lookup table like this, say in L1:M5 (it looks like you will
need more in your case)
0 A
20 B
35 C
65 D
135 E

In cell E2 enter the formula

=VLOOKUP(D2,L$1:M$5,2)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr. Low" wrote:

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641

Excel 2002: How to classify numbers ?
 

LOOKUP will take care of that..Now for the below formula there is a change
from your initial conditions...Try and feedback

=IF(C2="","",LOOKUP(C2,{0,21,36,66,136},{"A","B"," C","D","E"}))

If this post helps click Yes
---------------
Jacob Skaria


"Mr. Low" wrote:

Hi Jacob,

Thanks for the first formula.

What about the formula with "greater or equal" and "less or equal" as
illustrated below:

Group defination:
0 =< A <= 20
21 =< B <= 35
36 =< C <= 65
66 =< D <=135
136 =< E

Thanks

Low


--
A36B58K641


"Jacob Skaria" wrote:

In E2 copy the below formula
=LOOKUP(D2,{0,1,20,35,65,135},{"","A","B","C","D", "E"})

If this post helps click Yes
---------------
Jacob Skaria


"Mr. Low" wrote:

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641

Excel 2002: How to classify numbers ?
 

Hi Jacob,

Thanks for your help.

Low
--
A36B58K641


"Jacob Skaria" wrote:

LOOKUP will take care of that..Now for the below formula there is a change
from your initial conditions...Try and feedback

=IF(C2="","",LOOKUP(C2,{0,21,36,66,136},{"A","B"," C","D","E"}))

If this post helps click Yes
---------------
Jacob Skaria


"Mr. Low" wrote:

Hi Jacob,

Thanks for the first formula.

What about the formula with "greater or equal" and "less or equal" as
illustrated below:

Group defination:
0 =< A <= 20
21 =< B <= 35
36 =< C <= 65
66 =< D <=135
136 =< E

Thanks

Low


--
A36B58K641


"Jacob Skaria" wrote:

In E2 copy the below formula
=LOOKUP(D2,{0,1,20,35,65,135},{"","A","B","C","D", "E"})

If this post helps click Yes
---------------
Jacob Skaria


"Mr. Low" wrote:

Hi,

Lets consider the folowing table:

A B C D E
1 Invoice Date Amount Days Old Group
2 B21 21.5.09 200 60 C
3 yyy 30 B
15 A
120 D
360 E
450
245
Group defination:
0 < A < 20
20 < B < 35
35 < C < 65
65 < D <135
135 < E

The group classification is a range where the number of days lies.

May I know the formula for cell E2 that enable me to get the answers when
copy downwards ?

Thanks

Low


--
A36B58K641