Projection of point on a line

maudit_mau · March 27, 2022, 8:15pm

hi, I have a problem with simple solution, but I’m not able to solve it by myself.
basically, given a line, I have to found a projected point.

for example look the blue line shown in the following chart:

Then, entering a specific CRF value, for example 19.6 or 19.8, I have to find the resulting size on the y1 axis.

So, how do I write an expression that given that blue line and a CRF value, can calculate the size?

Thanks.

EarnestAl · March 27, 2022, 9:03pm

You image is not visible. Use the up-pointing arrow icon with tooltip Upload to upload an image or a file.
Icons

It is easier for people to answer your question if you upload a LibreOffice file (make a short sample without private information) that shows the issue rather than a picture.
BTW I probably can’t help on the actual question.

maudit_mau · March 28, 2022, 9:04am

a part that I spent several minutes to understand why the image is not shown,
anyway when you click it, it is in another tab.
it seems that this forum auto-modifies DB links in the way for embedding them, I mean with
“dl.drobboxusercontent” at the beginning. but somehow the image isn’t shown in this page.

by the way, here’s the file:

Lupp · March 28, 2022, 12:22pm

In “Line” diagrams the horizontal axis generally is used for (consists of) labels. These labels aren’t expected to be given by equidistant numeric values, but only if they are, the diagram can be read the same way as if it is a “X-Y (Scatter)” diagram. The connection of points in a “Line” diagram is misleading because it always implies a danger to encourage the viewer to assume a functional dependency and a justification for interpolations (or even extrapolations) which may not correspond to something in the real world. See example
disaskAvoidLineCharts.ods (21.2 KB)

Best: Never use “Line” diagrams. Use column diagrams or bar diagrams instead.
Where something can be visualized by a “X-Y (Scatter)” diagram without lying, actually do it this way. With the exception of histograms (columns!) this is the proper way in next to every case in science, engineering, economy…
Where you can’t find a really good way to visualize something without the danger of grave misinterpretations (overinterprtetations) omit the visualization completely. A gap is better than a lie.
Never use “Donut” diagrams or any variants just invented for impressing the boss. Information needs to be information, not pretty toys!
Only use “innovative” diagrams if you actually want to lie. That’s their “raison d’être”. See xkcd: Y-Axis

mikekaganski · March 28, 2022, 9:25am

This should interpolate linear:

=INTERCEPT(A4:A5;C4:C5-B28)

maudit_mau · March 28, 2022, 2:45pm

thanks @mikekaganski, this is exactly what I was looking for.

at the beginning it returned an error like “#VALORE!” aka “#VALUE!”,
then I realized I needed to use the italian translated command for INTERCEPT,
that is INTERCETTA.

I think I’m going to change the LO language to english…
anyway INTERCEPT command perfectly works, thanks!

.

mikekaganski · March 28, 2022, 3:21pm

If you want that to use English spreadsheet function names, you don’t have to change UI language. Just check Options|Calc|Formula|[x] Use Enflish function names.

maudit_mau · March 28, 2022, 4:25pm

got it! thanks!

Lupp · March 28, 2022, 11:26am

Being an incurable teacher I couldn’t abstain from another attempt of teaching in this case.
See this attachment:
ask75750linearInterExtraPolation.ods (38.2 KB)

Also:

Please explain the concepts you used in examples and (roughly) the context. We are (I am) eager to learn about the real world as a side effect. of our visits to forums.
– What’s an “Average QP”?
– What’s a “CRF”
– Why should "Size (GB) and “Avg QP” be used for two vertical axes of the same diagram?
– By what are these quantities both related to “CRF”?
– Does this also imply a direrct relation between them?
Why did you use a “Line” type diagram in th first case, but a “X-Y (Scatter)” diagram in the second?
How comes any “percentage” in sight in your context. Do you aim at statistics?

maudit_mau · March 28, 2022, 3:08pm

– What’s an “Average QP”?
The average quantizer is basically a number that indicates how much an image or video has been compressed.

– What’s a “CRF”
Constant rate factor (CRF) is an encoding mode that adjusts the file data rate up or down to achieve a selected quality level rather than a specific data rate.

– Why should "Size (GB) and “Avg QP” be used for two vertical axes of the same diagram?
This is the only way I have found in order to show those 2 lines in the same chart.
.

– By what are these quantities both related to “CRF”?
– Does this also imply a direrct relation between them?
Changing CRF modifies both the size and the avg qp, so yes there’s a direct relation between them, sorry but I can’t currently explain it better.
.

- Why did you use a “Line” type diagram in th first case, but a “X-Y (Scatter)” diagram in the second?
I don’t know, it seemed to me the best type of visualizations for my needs.
.

- How comes any “percentage” in sight in your context. Do you aim at statistics?
I’m not sure I understand correctly, but in order to modify the right sided chart, I insert a Source value(B11), and then the sheet calculates the relative percentage values from 40% to 80%.
In this way I can compare the SIZE with the original video file size.