CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5481

Check if there is at least one value in column C that is bigger then the value in Column B (without Helper Column)

A B C D
1 Product sales_volume purchase_volume Check
2 Product_A 500 400 yes
3 Product_B 600 700
4 Product_C 300 250
5 Product_D 800 620
6 Product_E 100 100
7

In Cell D2 I want to have a formula that is doing the following:

= If there is at least one value in Column C > value in Column B then "yes" else "no"

I know I could achieve this with a Helper Column that subtracts the values from both Columns and then check the Helper Column for values >= 0.

However, I would prefer a solution without a Helper Column.

Do you have any idea if this is possible?

Upvotes: 1

Views: 1107

Answers (2)

Mr Shane
Mr Shane

Reputation: 646

If B2 is GREATER than the largest number in the range C2:C6, then "no", else "yes".

Try this formula in cell D2:

=IF(B2>MAX(C$2:C$6),"no","yes")

you can then drag the formula down to other cells

Upvotes: 0

Spencer Barnes
Spencer Barnes

Reputation: 2877

=IF(SUM(IF(C2:C6>B2:B6, 1, 0))>0, "yes", "no")
Be warned this is an array formula so might required you to press Ctrl+Shift+Enter after typing the formula instead of just inserting it normally

Upvotes: 1

Related Questions