Check out some similar questions!

Amyunimus · Jul 5, 2006, 11:39 PM

Question:

In 10 cells in a row, two unspecified values will appear. In all other cells, there is a zero. For example:

A B C D E F G H I J K
0 61 0 0 28 0 0 0 0 0 0

I have many rows, and the column in which they appear is randomized. In each row, one of the two values falls in the "correct" location. Let's say here, I know the number in column "B" is correct.

Using this information, I can list "61" in a separate column. But is there any way to find the second number, "28" without having the additional information of where it is located?

Thanks.

ScottGem · Jul 6, 2006, 07:23 AM

I'm not sure I follow what you want. But there are some things that you might be able to do.

Lets say you want to just show the second value in a separate column. The formula:

=Sum(A1:K1) - B1 Will return 28

You could also create a custom function that loops through each column in the row and determines which has a non zero value.

colbtech · Jul 6, 2006, 09:02 AM

Can you give us more info on what you are trying to achieve.

It's easy enough to display the values if they are non-zero, but??

kp2171 · Jul 6, 2006, 09:12 AM

if the values are truly randomized and you have many rows then you'd see the pattern for both the "proper" position for both values quite quickly (with just a few rows), as you state that each row must have one of the two numbers in the right place.

Amyunimus · Jul 6, 2006, 08:17 PM

Thanks-- summing the rows and then subtracting the correct number works in this case (ScottGem's suggestion). Thank you!

ScottGem · Jul 7, 2006, 06:45 AM

Glad to assist