+1 vote
in General IT Tips by (75.0k points)
I want to compare 3 columns in Excel and return 1 or 0 depending on the result.

E.g.

If the value in column A < Column B < Column C, i.e., the value in Column B is between the values in Columns A and C, then return 1, otherwise 0.

1 Answer

+2 votes
by (353k points)
 
Best answer

You can use the following Excel formula to achieve this:

=IF(AND(A1 < B1, B1 < C1), 1, 0)

The formula checks if the value in cell A1 is less than the value in cell B1 AND if the value in cell B1 is less than the value in cell C1. If both conditions are true, it returns TRUE; otherwise, it returns FALSE. If AND(A1 < B1, B1 < C1) is TRUE, it returns 1, otherwise, 0.

You can change the cell number to apply the formula to other rows.


...