CODEWITHSUNDEEP
excelexcel-formulaexcel-2010ms-office
Student222
Student222

Reputation: 3061

What does this formula mean in excel? (A cell equals a range)

I see the following formula in a Excel spread sheet and can not understand... Can anyone explain what the test condition "N5=N4:N741" mean?

=MIN(IF(N5=N4:N741,K4:K741))

I made some experiments and still cannot get a clue...

enter image description here

enter image description here

Upvotes: 4

Views: 231

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

I'm assuming this is an array formula.

What this does is takes the minimum of the range K4:K741 where the value in N4:N741 equals the value in N5.

Let's look at a smaller example. K4:N9 is shown below.

    K L M N 
  ----------
4 | 4     2
5 | 8     7
6 | 3     4
7 | 2     1
8 | 7     9
9 | 1     7

The expression N5=N4:N9 is true in row 5 and row 9 since both of those match N5 (value = 7), giving the array {False,True,False,False,False,True} Thus IF(N5=N4:N9,K4:K9) will return {False,8,False,False,False,1} since the True values are replaced by the corresponding row in column K. The MIN() function will then ignore the False parts and return the minimum of the corresponding values in column K (the value 1 since 1 < 8).

Upvotes: 3

Oakland Ne
Oakland Ne

Reputation: 1

I believe it returns an array of true and false values. I also believe the true shows up for the 3 because it is the third item in the array. but that is a guess on my part.

{false, false, true, false,false}

If you change your 5 in E1 to a 1, it will return a true.

Research all of many things excel

Upvotes: 0

Related Questions