SOLVED

Formula to check equal or greater than 2 among results

Copper Contributor

Good morning,

I have to do a very speciffic query where I have some columns with different data and I was wondering if I could create a formula in order to do it automatically.

The thing is that I need to know if any of the columns value is the double (or more than the double) higher than the second highest value between these columns. And to obtain as an answer the lablel of the column that has this satisfactory value.

For example, imagine we have:

Language    SVO   SOV   VSO  VOS  OSV  OVS
Akkadian     6         29      0       0      6       0

In this case, the value in the SOV column is the double or more higher than the second highest value (SVO or OSV), so the answer would be: SOV.

If there is no column with this double value, the result would be: no dominant order.

Is that technically possible? I know it's very speciffic and I can't reach a formula that fits these requierements.

2 Replies
best response confirmed by ToniBrosa (Copper Contributor)
Solution

@ToniBrosa 

=IF(LARGE(B2:G2,1)>=LARGE(B2:G2,2)*2,INDEX($B$1:$G$1,MATCH(LARGE(B2:G2,1),B2:G2,0)),"no dominant order")

Does this return the expected result?

greater or equal.JPG 

@OliverScheurichamazing!!!!!

I was having problems because it's quite a complex query (it seems for you it wasn't).

Thank you so much. I've done a short test and it seems that works perfectly.

1 best response

Accepted Solutions
best response confirmed by ToniBrosa (Copper Contributor)
Solution

@ToniBrosa 

=IF(LARGE(B2:G2,1)>=LARGE(B2:G2,2)*2,INDEX($B$1:$G$1,MATCH(LARGE(B2:G2,1),B2:G2,0)),"no dominant order")

Does this return the expected result?

greater or equal.JPG 

View solution in original post