I have the following information:

```  Height    Weight

170     65
167     55
189     85
175     70
166     55
174     55
169     69
170     58
184     84
161     56
170     75
182     68
167     51
187     85
178     62
173     60
172     68
178     55
175     65
176     70
```

In Excel, I want to create a quadratic and cubic regression analysis. I'm aware of how to perform it in Excel using linear regression, but what about cubic and quadratic functions? Despite doing extensive research, I was unable to come across anything useful.

Sep 30, 2022 in Others 198 views

## 1 answer to this question.

You need to use an undocumented trick with Excel's LINEST function:

```=LINEST(known_y's, [known_x's], [const], [stats])
```

Background

A regular linear regression is calculated (with your data) as:

```=LINEST(B2:B21,A2:A21)
```

which returns a single value, the linear slope (m) according to the formula:  is: Undocumented trick Number 1

You can also use Excel to calculate a regression with a formula that uses an exponent for x different from 1, e.g. x1.2: using the formula:

```=LINEST(B2:B21, A2:A21^1.2)
``` is: You're not limited to one exponent

Excel's LINEST function can also calculate multiple regressions, with different exponents on x at the same time, e.g.:

```=LINEST(B2:B21,A2:A21^{1,2})
```

Note: if locale is set to European (decimal symbol ","), then comma should be replaced by semicolon and backslash, i.e. =LINEST(B2:B21;A2:A21^{1\2})

Now Excel will calculate regressions using both x1 and x2 at the same time: How to actually do it

The impossibly tricky part there's no obvious way to see the other regression values. In order to do that you need to:

• select the cell that contains your formula: • extend the selection the left 2 spaces (you need the select to be at least 3 cells wide): • press F2

• press Ctrl+Shift+Enter You will now see your 3 regression constants:

```  y = -0.01777539x^2 + 6.864151123x + -591.3531443
```

Bonus Chatter

I had a function that I wanted to perform regression using some exponent:

y = m×xk + b

But I didn't know the exponent. So I changed the LINEST function to use a cell reference instead:

```=LINEST(B2:B21,A2:A21^F3, true, true)
```

With Excel then outputting full stats (the 4th paramter to LINEST): I tell the Solver to maximize R2: And it can figure out the best exponent. Which for you data: is: • 50,440 points

## Apache POI - watermark in Excel - different appearance in Excel and LibreOffice

There is nothing that apache poi could ...READ MORE

## Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

## How to add regression line equation and R2 on graph?

Below is one solution: # GET EQUATION AND ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE