|
In response to
"excel heads, what is "ROW" doing here? -- (edited)"
by
amanda
|
If I understand it correctly: -- (edited)
Posted by
oblique (aka kkuphal)
Apr 29 '22, 13:12
|
- The --(stuff) is doing checks (<= and >=) between the explicit cell and a range of numbers and generating an array of 0 and 1. So, for example, for 3 values if D8 <= C4, C5 or C6 you could end up with say 0, 1, 1 meaning false, true, true.
- There are two checks so you end up with two arrays like (0,1,1) and (1,1,0)
- The SUMPRODUCT is then taking the two "match" tests and multiplying and adding. Because of the multiplication, any false = 0 so it only works if you have two true. In the above example, you end up with
(0,2,0) because there was a zero in either of the arrays in the first and third positions and then added. Only the middle position had two 1s.
- Then the INDEX is using the SUMPRODUCT array to "index" the ROW. The true value will match up and so, if there was a true somewhere (which there will be), that true value will get returned from the value row.
|