677778_en-US

liliconcom

IDENTIFY LARGEST 3 VALUES TO CREATE INDICATOR COLUMNS

Hi,

I'm a trying to compare across column values (Utility A to Utility E) to identify the largest 3 values and put the results into indicator columns ("Top 3 - A" to "Top 3 - E").  So, if "Utility A" is one of the largest 3 values in the row, "Top 3 - A" would receive a "1".  If the value is NOT one of the 3 largest in the row, "Top 3 - A" would receive a "0". 

 

I tried using "Match", but wasn't able to get it to work.  Appreciate any help folks could provide!

 

Consumer and Market ResearchRe: IDENTIFY LARGEST 3 VALUES TO CREATE INDICATOR COLUMNS

Here is a formula that will work.  Just change the comparison column from A to B to etc.

If(
 :"Utility - A"n >= Sort Descending(
  Matrix( :"Utility - A"n ) || Matrix( :"Utility - B"n ) || Matrix( :"Utility - C"n ) ||
  Matrix( :"Utility - D"n ) || Matrix( :"Utility - E"n )
 )[3],
 1,
 0
)

Re: IDENTIFY LARGEST 3 VALUES TO CREATE INDICATOR COLUMNS

Thank you so much, Txnelson!  This is exactly what I needed!  I greatly appreciate you taking the time to share your expertise!!