CODEWITHSUNDEEP
excelexcel-formula
Adam12344
Adam12344

Reputation: 1053

Apply function if criteria met

I am trying to implement the SLOPE and INTERCEPT functions on data by group. My data looks like this:

X, Y, Group
1, 2, A
2, 4, A
3, 6, B
5, 9, B

I need the slope for Group A and Group B. My data is very long with many groups so I would prefer not to so this manually for all groups.

Upvotes: 0

Views: 71

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

Assuming your data starts at A1(the term 'X' is in A1), put this formula next to D2 and copy down if needed:

=SLOPE(INDIRECT("B" & MATCH($C2, $C$1:$C$5,0) & ":B" & MATCH($C2, $C$1:$C$5, 1)), INDIRECT("A" & MATCH($C2, $C$1:$C$5,0)  & ":A" & MATCH($C2, $C$1:$C$5, 1)))

Intercept is similar :

=INTERCEPT(INDIRECT("B" & MATCH($C2, $C$1:$C$6,0) & ":B" & MATCH($C2, $C$1:$C$6, 1)), INDIRECT("A" & MATCH($C2, $C$1:$C$6,0)  & ":A" & MATCH($C2, $C$1:$C$6, 1)))

If you wish to have the formula copied down alongside in columns D and E, BUT only want the slope appearing at the first occurrence, then wrap in an IF like so:

IF(MATCH($C2, $C$1:$C$5, 0)=ROW($C2), SLOPE(INDIRECT("B" & MATCH($C2, $C$1:$C$6,0) & ":B" & MATCH($C2, $C$1:$C$6, 1)), INDIRECT("A" & MATCH($C2, $C$1:$C$6,0)  & ":A" & MATCH($C2, $C$1:$C$6, 1))), "")

Same for Intercept...

Upvotes: 1

Related Questions