Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 : How to remove large page numbers ? | Excel Discussion (Misc queries) | |||
Excel 2002: How to add running numbers ? | Excel Discussion (Misc queries) | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
How do you classify an Excel user? | Excel Discussion (Misc queries) | |||
Excel 2002 - Filtering numbers | Excel Discussion (Misc queries) |