topic Re: IDENTIFY LARGEST 3 VALUES TO CREATE INDICATOR COLUMNS in Discussions

IDENTIFY LARGEST 3 VALUES TO CREATE INDICATOR COLUMNS

EH1 — Thu, 14 Sep 2023 14:00:25 GMT

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!

Re: IDENTIFY LARGEST 3 VALUES TO CREATE INDICATOR COLUMNS

txnelson — Thu, 14 Sep 2023 16:26:36 GMT

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

EH1 — Thu, 14 Sep 2023 17:14:33 GMT

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