Conditional Slope

Hi,

I have data of numbers in columns A and B from A21 to A30, and B2 to B30,

I'm looking for a function that can help me to get slopes out of A and B data (data on column A as X axis, and Y on the B), the idea is that if I type a letter (like "O" ) anywhaere in the column C like in cells C2,C3, C5, C7, for example then in the cell (D2) I get the slope of the points: (A2,B2) , (A3,B3) , (A5,B5) and (A7,B7)... and if I type at the same time "W" anywhaere in different cells
on the column C I get the slope of those points in the cell D4...
I attched an example to clarify the idea...

Appreciate help!

Hi,

I have data of numbers in columns A and B from A21 to A30, and B2 to B30,

I'm looking for a function that can help me to get slopes out of A and B data (data on column A as X axis, and Y on the B), the idea is that if I type a letter (like "O" ) anywhaere in the column C like in cells C2,C3, C5, C7, for example then in the cell (D2) I get the slope of the points: (A2,B2) , (A3,B3) , (A5,B5) and (A7,B7)... and if I type at the same time "W" anywhaere in different cells
on the column C I get the slope of those points in the cell D4...
I attched an example to clarify the idea...

Is that possible using excel functions ? in case I need a code, can you provide an example?

Appreciate help!

I'm not sure what you mean by slope of the O slope or W slope. The slope of those points doesn't change. In the attachement, all of your slopes are =1 and no desired result is given so its hard to see what you mean.
Could you give an example that includes the desired result.

(I presume that linear approximation is sufficiant or do you want to use a parabolic fit to your discrete points.)

Hi Ralem,

Deleted as equation was incorrect

hi Ralem
OK I was put off because with your example the slope will always be 1

in column d = xy fill down A1*B1
column e = x^2 fill down A1^2
=(COUNTIF(C2:C30,"O")*SUMIF(C2:C30,"O",D2:D30)-SUMIF(C2:C30,"O",A2:A30)*SUMIF(C2:C30,"O",B2:B30))/(COUNTIF(C2:C30,"O")*SUMIF(C2:C30,"O",E2:E30)-(SUMIF(C2:C30,"O",A2:A30)^2))

or the one formula
=(COUNTIF(C2:C30,"O")*(SUMPRODUCT((A2:A30)*(C2:C30="O"),(B2:B30)*(C2:C30="O")))-(SUMPRODUCT((A2:A30)*(C2:C30="O")))*SUMPRODUCT((B2:B30)*(C2:C30="O")))/(COUNTIF(C2:C30,"O")*(SUMPRODUCT((A2:A30)*(C2:C30="O"),(A2:A30)*(C2:C30="O")))-((SUMPRODUCT((A2:A30)*(C2:C30="O")))^2))

had to many () that were not needed
=(COUNTIF(C2:C30,"O")*SUMPRODUCT((A2:A30)*(C2:C30="O"),(B2:B30)*(C2:C30="O"))-SUMPRODUCT((A2:A30)*(C2:C30="O"))*SUMPRODUCT((B2:B30)*(C2:C30="O")))/(COUNTIF(C2:C30,"O")*SUMPRODUCT((A2:A30)*(C2:C30="O"),(A2:A30)*(C2:C30="O"))-SUMPRODUCT((A2:A30)*(C2:C30="O"))^2)

I updated the example; please have a look, it Doesn't matter what the slope is, what I'm after is that:
In cell E2 I need a function to do the folowing: if any of the cells C2:C30 equal 1 then give me in this cell (E2) the slope of data (X,Y) on the left of these 1s.

Same function for E4 and E6, coze I'l be having different groups of data with different slopes all sitting above each other, this function will help me to pickup these slopes and define them out.

Thanks

Pike,
you're unbelievable fabulous rabbit, many thanks..

Change D2, D4, and D6 to 1, 2, and 3 respectively (i.e., get rid of the "Slope = " part)

Name the data ranges X, Y, and Z

Then in E2,

=SUMPRODUCT( (Z=D2) * (X - AVERAGEIF(Z, D2, X) ) * (Y - AVERAGEIF(Z, D2, Y) ) ) / SUMPRODUCT( (Z=D2) * (X - AVERAGEIF(Z, D2, X) ) ^ 2 )

Or, to use the native function,

=SLOPE( IF(Z=D2, Y), IF(Z=D2, X))

Threads merged.

Ralem, please take a few minutes to read the forum rules before posting again.

Thanks shg, and sorry for cross-posting

shg,

your functions are interesting but couldn't apply them, please have a look at the attachment.

Look at the definition of range X compared to Y and Z.

Couldn't find it, any standard name for this definition to help searching?

In the Names box, left of the formula bar, enter X and look at the range selected.

Then enter Y, then Z. Do you see how X is different?

They must all be the same size.

Ah yeh I see, OK...
YES it works now, that's great... but for the second function I assume that it gives the slope of all the data from top to bottom not only the cells where there are 1s next to them, right?

Copy all the data pairs with some given number next to them to a contiguous range, and then apply the ordinary SLOPE. What result do you get?

Sorry, I didn't get that, can you please give me the answer?

The second function has the same dependency on Z that the first does.

My point was, you could do some investigation on your own to see how things behave, and you'd remember the results much better than if someone just gives you answers.

Learn to love the Evaluate Formula button on the Formula Auditing toolbar.

The second formula gives the same slope of this: =slope(Y,X), it doesn't give the slope of pairs those have 1 next to them, it doesn't pick them like the first one does?

See attached.

That's very nice shg, but it's different from what you provided before, there are 2 more brackets on the sides which I don't know how you enter them, when I try to edit my same formula by adding them, it just turns into a text, and if I click in the formula bar they just disapear???

It's an array formula, which means it MUST be confirmed with Ctrl+Shift+Enter. That's what makes the curly braces appear.

Ah, that's why... I didn't know that; thanks a lot shg... I really learnt a lot from this long thread..
appreciate your patience with me