excel multiple if statements and ranking

Microsoft Office excel 2007 - upgrade
December 22, 2010 at 01:03:59
Specs: Microsoft XP
hello there,
i need help as i need to rank 3 different groups from one data colum, i can separate the groups with and "IF" funtion and then i can rank those, however i need to have them in the same cell for working purposes

Name Group Total
AAB XY 256.22
AAC XZ
GHAG XX 241835.31
JERHJE XY 103.27
NEJ XZ
NMEK XX 2725.87
NMN XY 3071.4
NMEK XZ 0.01
KIUHJ XX 1110.45
JNEL XY 2113.48
NUE XZ 59723.16
AAAS XX
ENJKN XY 36755.94
SRJR XZ 128750.99
DEKE XX 5939.43
EEK XY 48364.91
UEJE XZ 24783.11
BER XX 54323

that needs to rank only within their groups.
can this be done??
thanks


See More: excel multiple if statements and ranking

Report •

#1
December 22, 2010 at 01:16:30
Name Group Total
AAB XY 256.22
AAC XZ
GHAG XX 241835.31
JERHJE XY 103.27
NEJ XZ
NMEK XX 2725.87
NMN XY 3071.4
NMEK XZ 0.01
KIUHJ XX 1110.45
JNEL XY 2113.48
NUE XZ 59723.16
AAAS XX
ENJKN XY 36755.94
SRJR XZ 128750.99
DEKE XX 5939.43
EEK XY 48364.91
UEJE XZ 24783.11
BER XX 54323


hope this looks better, the groups are XX,XY,XZ


Report •

#2
December 22, 2010 at 03:57:50
Please repost your data after reading the How To referenced below.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
December 22, 2010 at 04:09:25
Name	   Group	           Total
AAB	   XY	         256.22
AAC	   XZ	
GHAG	   XX	         241835.31
JERHJE	   XY	         103.27
NEJ	   XZ	
NMEK	   XX	         2725.87
NMN	   XY	         3071.4
NMEK	   XZ	         0.01
KIUHJ	   XX	         1110.45
JNEL	   XY	         2113.48
NUE	   XZ	         59723.16
AAAS	   XX	
ENJKN	   XY	         36755.94
SRJR	   XZ	         128750.99
DEKE	   XX	         5939.43
EEK	   XY	         48364.91
UEJE	   XZ	         24783.11
BER	   XX	         54323


*rank if group (XX...) rank total -(with group)-
		


Report •

Related Solutions

#4
December 22, 2010 at 05:20:52
Why not use Data...Text To Columns, then sort them with 2 criteria and then use CONCATENATE to put them back together?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
December 22, 2010 at 05:43:10
thanks but that will not give me what i need, i need it to look las per below, all in one formula!... Do you know how? i hope this helps

Ranked Cells According 
to Total and Group	

				
XY	XZ	XX	Name	Group	Total

5			AAB	XY	256.22
	5		AAC	XZ	
		1	GHAG	XX	241835.31
6			JERHJE	XY	103.27
	5		NEJ	XZ	
		4	NMEK	XX	2725.87
3			NMN	XY	3071.4
	4		NMEK	XZ	0.01
		5	KIUHJ	XX	1110.45
4			JNEL	XY	2113.48
	2		NUE	XZ	59723.16
		6	AAAS	XX	
2			ENJKN	XY	36755.94
	1		SRJR	XZ	128750.99
		3	DEKE	XX	5939.43
1			EEK	XY	48364.91
	3		UEJE	XZ	24783.11
		2	BER	XX	54323


Report •

#6
December 22, 2010 at 05:58:22
You are using Column labels (Name, Group, etc.) in your examples but you also said:

"I need to have them in the same cell"

I can't tell what is in which column. Please add A, B C, etc above your columns (before and after) so that I tell what you mean by "rank 3 different groups from one data column" and "I need to have them in the same cell".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
December 22, 2010 at 07:18:58
Apologies, what meant was i need the formula which
1.- find only the total if the group is XX,XY,XZ accordingly
2.- Rank the data according to ONLY the values which are on XX,XY,XZ accordingly

from my example, i have previously used a formula that has given me all the values according to the groups so fo exapmle cell A2 will have the formula =IF(E2="XY",F2,"")

XY (colum A), XZ (columB) etc... and rows 1,2,3, 4,... etc

what i need is a formula which i can put in A2 which will say, =IF(E2="XY",F2,"") and also Rank the data where it has found values...

does that explain it better??
please refer to the last table for the complete data

   A	B	C	D	E	F
1, XY	XZ	XX	Name	Group	Total
2,  5			AAB	XY	256.22
3,	5		AAC	XZ	
4,		1	GHAG	XX	241835.31


Report •

#8
December 22, 2010 at 18:40:44
OK,

I'm only going to claim about 10% credit for this.

The use of SUMPRODUCT for the ranking based on multiple criteria was not my idea. I found it via a Google search.

I simply adapted it to match your ranges and then added the IF portion to do the ranking based on the XX, XY and XZ column labels.

Just so there is no confusion I am using the layout you described in Response 7. Column labels in Row 1, data begins in Row 2. (In some of your earlier posts, there appears to be a blank row above the data. That's why it is important to use Column letters and Row numbers when posting. Since we can't see your spreadsheet from where we're sitting, you have to be very specific about your layout.)

Try this:

Enter this in A2 and drag it across to C2, then drag all three cells down to the bottom of your list:

=IF($E2=A$1,SUMPRODUCT(--($E$2:$E$19=A$1),--($F2<$F$2:$F$19))+1,"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
December 23, 2010 at 01:01:00
thats great!! many thanks!... can you please illustrate me in the actual impact the "--" have in a formula? i am still learning much about excel
kindest regards
Daniel

Report •

#10
December 23, 2010 at 09:08:19
The "--", also known as the double unary, is used to coerce the SUMPRODUCT into returning 1's & 0's as opposed to returning TRUE's and FALSE's.

There are other coercion methods (e.g. *1) but the double unary is considered the most efficient.

When SUMPRODUCT is used as a "logical test" (e.g. $E$2:$E$19=A$1) it will return an array of TRUE's and FALSE's. In order to SUM multiple arrays of TRUE's and FALSE's, we need to coerce them into being 1's and 0's.

Try this in a cell:

=TRUE
=-TRUE
=--TRUE

See the 5th post in the following thread for more detailed explanation, or Google something like sumproduct coercion

http://www.ozgrid.com/forum/showthr...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question