Excel formula to compare two columns

1
votes

I need a formula to compare two columns which will return true if all integer values in first column are not less then all integer values in second column.

Can anyone help me with this?

UPDATE:

I need to do some calculations if numbers in each row in column H greater then value in column I (picture below). I need this formula to return TRUE or FALSE because I will use it in another more complex formula.

example picture

UPDATE:

I form this excel file from Java code and I need include this formula to the cell. It's not good to use any extra columns in this case.

3
javaexcelif-statementexcel-formulacompare
any example of what you have tried or how it should look would help. This would leave many guessing, not what SO is about imo. – krib
@krib added some information to the question. – Ilya Tkachou
@pnuts any amount, sorry for bad example – Ilya Tkachou

3 Answers

1
votes

The array formula

{=AND(H6:H32>I6:I32)}

will give you TRUE if each value in column H is greater than the corresponding value in I (so H6 greater than I6 and H7 greater than I7 and ... and H32 greater than I32) and FALSE otherwise.

Array formulae are entered through Excel's GUI using CTL + SHIFT + ENTER keyboard combination to commit the formula or by setting a cell's FormulaArray property in VBA.

Alternatively, this non-array formula

=SUMPRODUCT(1*(H6:H32>I6:I32))=27

will also deliver the same result but in a less elegant way.

1
votes

i would do it in this way:

result of each line (here C2): =IF(A2>=B2;1;0)
and then the end result (here C5): =IF(COUNTIF(C2:C4;0)>0;TRUE;FALSE)

0
votes

If for example you have column A and B, then try this in column C: 

=(B1<=A1)

It will return as True or False in column C