Comparing Columns

Question

Level 1

0 points

Comparing Columns

Hey guys,

I'm trying to compare the values in 2 columns and return the value in a third column.

If column B > column c then return value in column a.

I would use nested if statements but there are 100 cells to compare and I don't feel like typing that much.

MacBook, Mac OS X (10.5.5)

Posted on Oct 13, 2008 7:28 PM

Reply

Answer 1

pw1840

Level 3

860 points

Oct 13, 2008 8:32 PM in response to Klings71

Klings,

Klings71 wrote:

I'm trying to compare the values in 2 columns and return the value in a third column.

In the header of column A, place the formula: =B-C. This will automatically place your formula in all rows of column A and give you the comparison of columns B and C.

If column B > column C then return value in column a.

In this case for column A, in the header column enter the formula: =IF(B>C,B-C,"") This will give the comparison of columns B and C only if B>C. If B is not greater than C, A will be left blank. Again, typing the formula in the header of column A will place the formula in all rows of column A.

I would use nested if statements but there are 100 cells to compare and I don't feel like typing that much.

I don't see how you were thinking nested IF() statements would be used unless you have something different in mind to what has been described above. In this case you will have to clarify your question.

pw

Reply

Answer 2

KOENIG Yvan

Level 8

41,807 points

Oct 14, 2008 2:31 AM in response to Klings71

Am'I tired or are these two sentences meaningless?

I'm trying to compare the values in 2 columns and return the value in a third column.

If column B > column c then return value in column a.

I tried to guess what they means and assumed that you want to define the result this way:

if B > C then result is A
what when B is less or equal to C ?

If you sheet has a header row you may enter

=if(B>C,A,"???")
in cell D1

I let you decide what you want in lieu of "???".

An other way to understand your question may be:

return in A the result of the comparison between B and C

Always assuming that you have a header row, you may achieve this goal entering
=B>C in A1

You will get
TRUE when B is greater than C
FALSE when it is not.

As always, when a problem is correctly described, it's quite solved 😉

Yvan KOENIG (from FRANCE mardi 14 octobre 2008 11:27:13)

Reply

Answer 3

Klings71 Author

Level 1

0 points

Oct 18, 2008 10:06 PM in response to KOENIG Yvan

KOENIG Yvan wrote:
Am'I tired or are these two sentences meaningless?

So here's the deal KOENIG, hop on some redbull and work with me,

Column A is an independent variable. Column B is a variable dependent on column A, as is C.

To calculate the values I have to use kinematic equations
Column B =A22*$F$8
Column C =($F$9*A22)+(0.5*$F$11*A22^2)

What I would like to do is find the instance(row) where the value in C surpasses the value in B, and then display the value from A in the highlighted cell where I typed the search equation. I don't want to create another column devoted to comparing the values in B and C...that's easy, and I can manually search for the first TRUE and go from there. The equation is to be typed in one cell and return only the first instance where column C is greater than column B. This is easy in C++ or FORTRAN but I don't know how to write search arrays for Numbers.

Recap, I need to search for the first instance when C > B, and then display the value from the corresponding column A in the highlighted cell.

Reply

Answer 4

KOENIG Yvan

Level 8

41,807 points

Oct 19, 2008 3:43 AM in response to Klings71

OK

With the complementary infos, the problem is clearly defined.

The response also.
Given the described rules we can't do what you asked.

Numbers is not C++, not Fortran, not Excel.
It doesn't offer functions dealing with arrays or matrix.

Of course we may get the wanted result but not with your rules.

Here, row 1 is an header one
in cell B1 I entered:
=IF(ISBLANK(A),"",ROUND(A*$F$1,2))

in cell C1 I entered:
=IF(ISBLANK(A),"",(A*$F$2)+(0.5*$F$3*A^2))

in cell D1 I entered:
=IF(OR(ISBLANK(C),C=0,C<B),"",A)

in F1 I entered 124.667
in F2 I entered 0
in F3 I entered 3

which are the constants used by your sheet.

In D54 (row 54 is a footer row) the ormula is
=MIN(D)

Yvan KOENIG (from FRANCE dimanche 19 octobre 2008 12:42:46)

Reply