Populate Cells based on Criteria of Another Cell

Hi there,

What I'm trying to do is create a formula that multiplies the value of a cell by negative one if the value of another cell is in one of the top three largest value's is its array.

For example, I want to multiply TRY's value in column L by -1 if it's corresponding rank (in column A) is in the top three of the row.

The idea is to multiply the numbers is L:V by negative one if it's rank is in the top three of the row.

Any help is so appreciated!!!



A B C D E F G H I J K
Rank
TRY BRL MXN ZAR KRW INR PLN CLP MYR THB CZK
7 6 5 4 1 #N/A 3 #N/A #N/A 2 #N/A
9 8 7 6 1 #N/A 5 4 #N/A 3 2
8 7 6 5 1 #N/A 4 3 #N/A #N/A 2

L M N O P Q R S T U V
TRY BRL MXN ZAR KRW INR PLN CLP MYR THB CZK

-0.007 -0.002 -0.001 -0.019 -0.005 -0.001 -0.025 0.011 #N/A -0.006 -0.015
-0.005 -0.020 -0.004 -0.011 -0.005 -0.006 -0.013 -0.008 -0.002 -0.009 -0.012
0.000 0.002 0.005 0.001 -0.011 -0.001 0.000 0.008 -0.001 -0.005 -0.001
-0.003 -0.003 -0.007 -0.015 0.001 -0.003 -0.004 0.006 -0.004 -0.011 -0.004

I'd use an IF function, and inside is, use the LARGE function as the basis for your criteria.

I don't think LARGE will accept an array as an argument. I'd replace with
=IF(A4=OR(LARGE($A4:$K4,1),LARGE($A4:$K4,2),LARGE($A4:$K4,3)),L4*-1,L4)

Can you post a sample spreadsheet? It's really difficult for me to test out a formula and figure out what isn't working without something to test it on.

The LARGE function doesn't like that there are error values in the array it's evaluating. I'd add something like IF(targetcell="","",targetcell) to the source data so that you only have numbers and blanks to lookup from, no errors to confuse it.

If that solved your problem, please remember to mark your thread solved.

Nice combination.