Using the IF function to specify which rows to use for SLOPE

[milkychips]

Hi all

Please help.
I am unsure whether I need to use the INDEX function or not, though I suspect not.

There is data in A14:K500
A14:A500 contains different 3 letter abbreviations such as SFL and ISK
I would like to calculate the slope, =SLOPE(D14:D500,B14:B500)
but only using data from the lines where column A contains "SFL", for example.

Also, how would I do it if I wanted to find the slope if A: contains either SFL or ISK


Thanks
Marco

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[milkychips]

I have renamed the post, sorry.

Could you please reopen the thread

Thanks
Marco

[MrShorty]

A sample spreadsheet would allow us to better help with specific solutions.

According to the help file (https://support.office.com/en-us/art...rs=en-US&ad=US ) the slope function will ignore text, logical, or empty cells. I tend to use helper columns for this kind of thing. I would use a helper column =IF(criteria="sfl",xvalue,FALSE) for the xvalues and =IF(criteria="sfl",yvalue,FALSE) for the yvalues, then feed those two helper columns into the SLOPE() function. You can use an AND() or OR() function in the first argument of the IF() function to test multiple criteria.

[milkychips]

Thanks Mr Shorty.

Could you please help me feed it into a formula.
I have attached a simplified spreadsheet, A1:G11, with D to G the helper columns you suggest for SFL and ISK.

What would I need write in both J13 and J14 to get the slope for those codes only?

[milkychips]

As you said, the SLOPE function ignores text, logical, or empty cells so =SLOPE(E2:E11,D2:D11) works fine for SFL only.

Thanks for your guidance