Im trying to calculate values to comple data labels base on Logaritmic trendline

So, with only two points, it is basically "linear interpolation" between those two points, correct?

Standard linear interpolation formula (Y2-Y1)/(X2-X1)=(Y3-Y1)/(X3-X1) where points 1 and 2 are the known points and point 3 is the one to find. Solve for Y3
Y3=(Y2-Y1)/(X2-X1)*(X3-X1)+Y1
Note that, in this case, X's are really ln(x).
Substitute references for variables. formula in C5 =($C$11-$C$4)/(LN($B$11)-LN($B$4))*(LN(B5)-LN($B$4))+$C$4. Note the mix of relative and absolute references and copy/paste into C5:C10.

Will that work?

Not sure why it did not work.

Perhaps you are referring to a formula like =-0.736*LN(B5)+3.93 or =$A$1*LN(B5)+$A$2 where A1 and A2 contain the constants from the trendline? Is that what you are wanting to do?

My mistake. I notice you are using a line chart (category horizontal axis) and not a scatter chart (numeric horizontal axis). When using a trendline on a line chart, the trendline uses "count numbers" (1,2,3,...) for the known x values. If you had the numbers 1 to 8 in A4:A11, you could use =-0.736*LN(A5)+3.93 to fill in column C -- assuming, of course, that you are required to use a line chart for this.

One usually prefers to use the actual x values rather than arbitrary count values, in which case, one should use an XY scatter chart. If you change the chart type, you will see that you get a regression equation of y=-8.173*LN(x)+32.749. Putting these constants into the formula will allow you to use column B as the x values in your formula.

If you are not required to use the chart at all, and you don't like the linear interpolation formula, I find I prefer to do regressions directly in the spreadsheet using one of the built in regression functions (like LINEST(), SLOPE(), INTERCEPT(), TREND() and others). For example, if I make a table for the known values.

Please Login or Register  to view this content.

I can use =TREND($O$2:O$O3,$N$2:$N$3,LN(B5)) to get the values. I can use =LINEST(O2:O3,N2:N3) to get the regression constants. Whether or not you can use this kind of approach depends on what changes to the sheet you are allowed to make and how you will want to interact with the sheet.