Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
I have a list of 4 clients:
A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
Try this:
JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 =IF(B1="","",SUMPRODUCT(--(B1<B$1:B$4),1/COUNTIF(B$1:B$4,B$1:B$4&""))+1) Copy down as needed. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... I have a list of 4 clients: A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
Lookup RANK in help.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "FARAZ QURESHI" wrote in message ... I have a list of 4 clients: A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
Use rank such as
=RANK(B1,$B$1:$B$4) the first is the number for the individual, then a comma, then the entire range of values. One exception to rank is that a tie would both be given 1, the next would be given a 3 not a 2. -- -John Please rate when your question is answered to help us and others know what is helpful. "FARAZ QURESHI" wrote: I have a list of 4 clients: A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
Thanx Biff
But where should I place this formula? I want it to be placed on the range B1:B4, i.e. against the names, so as to rank the corresponding statistics in A11:B14. "T. Valko" wrote: Try this: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 =IF(B1="","",SUMPRODUCT(--(B1<B$1:B$4),1/COUNTIF(B$1:B$4,B$1:B$4&""))+1) Copy down as needed. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... I have a list of 4 clients: A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
Thanx John & Bob,
But ranking I guess would not help as the names are in other range and I want to lookup SUM of the party's corresponding amounts in another range to determine its position/rank. "Bob Phillips" wrote: Lookup RANK in help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "FARAZ QURESHI" wrote in message ... I have a list of 4 clients: A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Position/Level of statistics
You should use an intermediate step of getting the totals and ranking
against those totals. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... Thanx Biff But where should I place this formula? I want it to be placed on the range B1:B4, i.e. against the names, so as to rank the corresponding statistics in A11:B14. "T. Valko" wrote: Try this: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 =IF(B1="","",SUMPRODUCT(--(B1<B$1:B$4),1/COUNTIF(B$1:B$4,B$1:B$4&""))+1) Copy down as needed. -- Biff Microsoft Excel MVP "FARAZ QURESHI" wrote in message ... I have a list of 4 clients: A 1 JOSEPH 2 MARY 3 DAVID 4 ADAMS Next I have list of transactions: A B 11 JOSEPH 200 12 MARY 300 13 DAVID 400 14 ADAMS 300 12 MARY 1000 13 DAVID 900 14 ADAMS 700 So the totals of each client come upto: JOSEPH 200 MARY 1300 DAVID 1300 ADAMS 1000 I want to devise a formula for the range B1:B4 reflecting the position of each client as follows: A B 1 JOSEPH 3 2 MARY 1 3 DAVID 1 4 ADAMS 2 Because Mary & David have the highest amount of transactions they should be numbered 1, Adams is the next so 2 and Joseph, being the least, 3. Had Mary & David possess different highest amounts they would have been marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4 respectively. All the help in this regard would highly be appreciated! Thanx in advance, Regards FARAZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In descriptive statistics, what does the "confidence level" mean? | Excel Worksheet Functions | |||
In descriptive statistics, what does "confidence level" mean? | Excel Discussion (Misc queries) | |||
In descriptive statistics, what is the "confidence level"? | Charts and Charting in Excel | |||
p-value, statistics | Excel Discussion (Misc queries) | |||
multi-level subtotals are in the wrong position | Excel Worksheet Functions |