RANK NUMBERS FROM 5 COLUMNS INTO 1

[Data123]

Hi does anyone know how to create a formula to calculate the values from 5 columns to then rank them in one separate column?

The 5 columns have values that include negative numbers.

Also, is it possible to specify a weight to each column being calculated? Meaning, I would like to limit some columns whose values are less important than others.

Thanks!

 

[dermie_72]

maybe if you could show us what the original data looks like and what you're expecting to see. What identifies it as more or less important. Is that a constant number or a variable?

 

[Data123]

maybe if you could show us what the original data looks like and what you're expecting to see. What identifies it as more or less important. Is that a constant number or a variable?

Good point a visual is always better. To make things easier I just added 3 columns below instead of 5. If I just want to add the 3 columns together then divide by three to get the average is it possible (column 1 row 2 + column 2 row 2 + column 3 row 2 = X then divide X by 3 = Y)? Is it possible to then rank the order. Meaning get the average and rank the results instead of showing the values (Y) in one column?

Column 1
500
200
100
0
-4
-500

Column 2
4
3
2
0
-1
-4

Column 3
50
9
-20
7
40
20

 

[awoohaw]

I'm not entirely sure this is what you want. But, I could not build the RANK function into a Let Statement so the average and rank are in separate columns:

Book1
	A	B	C	D	E
1	Column 1	Column 2	Column 3	AVG	RANK OF AVG
2	500	4	50	184.6667	1
3	200	3	9	70.66667	2
4	100	2	-20	27.33333	3
5	0	0	7	2.333333	5
6	-4	-1	40	11.66667	4
7	-500	-4	20	-161.333	6
Sheet4

Cell Formulas
Range	Formula
D2:D7	D2	=LET(A,A2:A7,B,B2:B7,C,C2:C7,ABC,A2:C7,AVGABC,BYROW(ABC,LAMBDA(ABC,AVERAGE(ABC))), AVGABC)
E2:E7	E2	=RANK(D2,$D$2#,0)
Dynamic array formulas.