Dont know how to Select Cell Range For SLOPE Formula

Question

Here is my problem. I want to use =SLOPE formula with a cell range that refers to another sheet with criteria: Date period between Oct-21 until Apr-22, the known_ys = Emiten Name and known_xs = IHSG. I use this formula:

=SLOPE(IF(AND(Emiten!B:B>=Risk!$A$2;Emiten!B:B<=Risk!$B$2;Emiten!A:A=Risk!D17);Emiten!D:D);IF(AND(Indeks!B:B>=Risk!$A$2;Indeks!B:B<=Risk!$B$2;Indeks!A:A=Risk!$D$2);Indeks!D:D))

But it shows the result #VALUE!, can anyone please tell me where's the problem?

Thanks

Answer 1

AND only ever returns a single value. To simulate 'and' conditions within array constructions, use nested IF statements.

Also, you should not be using entire column references for such a construction, since it will be forced to process several million rows unnecessarily.

Try:

=SLOPE(IF(Emiten!B1:B10>=Risk!A2,IF(Emiten!B1:B10<=Risk!B2,IF(Emiten!A1:A10=Risk!D17,Emiten!D1:D10))),IF(Indeks!B1:B10>=Risk!A2,IF(Indeks!B1:B10<=Risk!B2,IF(Indeks!A1:A10=Risk!D2,Indeks!D1:D10))))

adjusting the last row referenced (10 here) to a suitably low, though sufficient, upper bound.

Also, I take it you are aware that the SLOPE function will only consider rows for which both the known_ys and the known_xs are numeric? For example, assuming that the above resolved to:

known_ys	known_xs
14	FALSE
53	FALSE
FALSE	27
FALSE	FALSE
16	63
58	90
FALSE	FALSE
3	FALSE
80	85
25	40

then SLOPE would ignore everything apart from the the 5th, 6th, 9th and 10th pairs, effectively calculating over the following reduced array:

known_ys	known_xs
16	63
58	90
80	85
25	40