ExcelBanter - Conditional Array Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Conditional Array Formula (https://www.excelbanter.com/excel-worksheet-functions/153780-conditional-array-formula.html)

IPerlovsky

Conditional Array Formula

I often use some variation of the following formula to achieve conditional
MAX, MIN, AVERAGE, etc.

=MAX(IF((J2:J2000=E8)*(K2:K2000=E9),M2:M2000))

How would I change this formula to achieve a conditional array formula such
as INTERCEPT, SLOPE, etc.
--
iperlovsky

Lori

Conditional Array Formula

=SLOPE(IF((J2:J2000=E8)*(K2:K2000=E9),M2:M2000),N2 :N2000)

array-entered works in xl2003 and similarly for INTERCEPT, RSQ, FORECAST.

"IPerlovsky" wrote:

I often use some variation of the following formula to achieve conditional
MAX, MIN, AVERAGE, etc.

=MAX(IF((J2:J2000=E8)*(K2:K2000=E9),M2:M2000))

How would I change this formula to achieve a conditional array formula such
as INTERCEPT, SLOPE, etc.
--
iperlovsky

IPerlovsky

Conditional Array Formula

brilliant, thank you...
--
iperlovsky

"Lori" wrote:

=SLOPE(IF((J2:J2000=E8)*(K2:K2000=E9),M2:M2000),N2 :N2000)

array-entered works in xl2003 and similarly for INTERCEPT, RSQ, FORECAST.

"IPerlovsky" wrote:

I often use some variation of the following formula to achieve conditional
MAX, MIN, AVERAGE, etc.

=MAX(IF((J2:J2000=E8)*(K2:K2000=E9),M2:M2000))

How would I change this formula to achieve a conditional array formula such
as INTERCEPT, SLOPE, etc.
--
iperlovsky

All times are GMT +1. The time now is 01:47 AM.