[Solved] Graphing Intersection of Trend Lines

Discuss the spreadsheet application
Post Reply
hmblm12
Posts: 1
Joined: Mon Feb 08, 2010 2:06 pm

[Solved] Graphing Intersection of Trend Lines

Post by hmblm12 »

Hello,

I'm supposed to find the x and y values for two functions
so on my graph two trend lines intersect and I need to find the exact point at which they do so.
Is it possible to make it so that calc sets the two equations equal to each other then solves for a variable?
Last edited by MrProgrammer on Sat Dec 19, 2020 5:04 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 3.1.1 on Windows XP(SP2)
ken johnson
Volunteer
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Graphing Intersection of Trend Lines

Post by ken johnson »

Say you have the formulas for the two trend lines in the form of...
ax+by=c
dx+ey=f
You could enter the constants into A1:C2...
A1=a B1=b C1=c
A2=d B2=e C2=f

Then in any other cell type an equals sign followed by...
MMULT(MINVERSE(A1:B2);C1:C2)
then press Ctrl+Shift+Enter to enter this array formula.
It's a multivalued array formula that returns two values. The top value is the x coordinate and the lower value is the y coordinate of the point of intersection.

If you are wondering why I didn't add the equals sign myself, it was to prevent you from copying the whole formula then pasting it into a cell. Array formulas can fail to work when transferred this way.

Ken Johnson
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Fitch
Posts: 115
Joined: Mon Feb 22, 2010 6:01 pm

Re: Graphing Intersection of Trend Lines

Post by Fitch »

I know this is an old post and I'm probably doing the wrong thing reviving it, but my problem is similar except one line is horizontal.
Is it necessary to go through the whole array stuff ?

I have a line that starts at x=.01, y=18
and ends at x=.05, y=91

The horizontal line is y=75, with start and end x as above.
The vertical line I am hoping to get should intersect at around x=.04
LibreOffice 5.1.4.2
Xenial Xerus
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Graphing Intersection of Trend Lines

Post by MrProgrammer »

=FORECAST(75;{0.01;0.05};{18;91})
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Fitch
Posts: 115
Joined: Mon Feb 22, 2010 6:01 pm

Re: Graphing Intersection of Trend Lines

Post by Fitch »

Aahh! another formula I'd never heard of.
Too easy.
Thank you very much.
LibreOffice 5.1.4.2
Xenial Xerus
Post Reply