Excel Question

[Cavaliere]

Hi All,

I'm sure there's a simple answer, but i don't know it and i'm dumb.

I have 2 columns: column A is a number, and column B is a percentage. Column B is dependent on column A. B tends to get bigger when A gets smaller, and vice versa. You are given historical values for A and B. What formula/function could i use to predict the value of B given A. I was thinking of using a regression, but i feel like that isn't right.

Example, Solve for x: (I originally had a space between the 2 columns, but this site formatted it by removing it. I put an underscore instead)

Column A______ Column B
2176.45 ______ 75.56%
1097.8 ______ 80.69%
2036.03 ______ 76.36%
2214.75 ______ 75.36%
2004.12 ______ 76.56%
1984.97 ______ 76.68%
2521.15 ______ 74.18%
2233.90 ______ x%

[Dr T Non-Fan]

Cubic Splines?

Excel can apply a number of different regressions: linear, exponential, quadratic, etc.

Would help to know what, in general, the dependence is based on.

[Childish Gambino]

That's not an excel question. There are numerous models one could apply. You have to decide that and then if you don't know how to code your preferred model, we could help you with that.

[DeepPurple]

Originally Posted by Cavaliere

Hi All,

I'm sure there's a simple answer, but i don't know it and i'm dumb.

I have 2 columns: column A is a number, and column B is a percentage. Column B is dependent on column A. B tends to get bigger when A gets smaller, and vice versa. You are given historical values for A and B. What formula/function could i use to predict the value of B given A. I was thinking of using a regression, but i feel like that isn't right.

Example, Solve for x: (I originally had a space between the 2 columns, but this site formatted it by removing it. I put an underscore instead)

Column A______ Column B
2176.45 ______ 75.56%
1097.8 ______ 80.69%
2036.03 ______ 76.36%
2214.75 ______ 75.36%
2004.12 ______ 76.56%
1984.97 ______ 76.68%
2521.15 ______ 74.18%
2233.90 ______ x%

This data is really darn linear. The formula you seek is +A9*INDEX(LINEST(B2:B8,A2:A8),1)+INDEX(LINEST(B2:B 8,A2:A8),2)

This is Y = X * m + b simple.

[Sredni Vashtar]

Select both columns, insert a graph/scatter-plot.
Does it look like a line? A curve? A bunch of random dots?