November 9, 2020

I have some data on company sizes and want to chart this and extrapolate to find say, the number of companies with 100 employees, or 200 employees, or 1000 employees etc.

Number of employees |
Number of companies |

1 - 9 | 1,187,045 |

10 - 49 | 217,240 |

50 - 249 | 35,940 |

250+ | 7,675 |

or even to extend the table to the first column is in smaller buckets.

Again, any help appreciated. Thanks

November 8, 2013

Hi Robert,

Here is a UDF function for linear interpolation I created a while ago:

Option Explicit

Function Interpolate(Y As Double, Tbl As Range, _

RdColData As Long, Optional VerticalTable As Boolean = True)

Dim HorizontalTable As Label

'Catalin Bombea - Iasi, 2013

'first argument - TargetVal As Double is the reference value

'second argument - Tbl As Range,, is the data range;

'the third argument - RdColData As Integer, represents the column number of the data range, from this column the function will extract values.

'(must be set to Long instead of integer, for large tables of data)

'If you want to return results from second column, set this argument to 2.

'in horizontal tables, if you want to return data from 3'rd row, set this argument to 3.

'This argument can be 1, but because the function will always look for the reference value in first column for vertical data tables,

'or in first row for horizontal data tables, it will return the same data type as the reference value

'last argument-Optional VerticalTable As Boolean = True : "TRUE or FALSE. We should use TRUE (this argument is optional, the default value is TRUE)

'for vertical tables, if we have data in 2 or more columns;

'If the table is horizontal, (with data in 2 or more rows, but several columns of data), FALSE value must be used.

'IF FALSE is used, then the numeric argument will represent the ROW number to return values from, instead of COLUMN number).

'IMPORTANT:

'If this argument is TRUE, the function will look in the first COLUMN of the data range to find the reference value,

'and it will return values from one of the next columns, based on the value of the third argument; if this argument is FALSE,

'the function will search the reference value in the first ROW of the data table, and it will return values from one of the next rows,

'based on the value of the third argument."

Application.Volatile

Application.ScreenUpdating = False

Dim Y1 As Double, Y2 As Double, X1 As Double, X2 As Double, i As Long

If VerticalTable = False Then GoTo HorizontalTable

'when the loop ends, the next item is Y2, the closest higher value from data table

For i = 1 To Tbl.Rows.Count

If IsNumeric(Tbl.Cells(i, 1)) And Tbl.Cells(i, 1) > Y Then

Exit For

End If

Next i

Y1 = Tbl.Cells(i - 1, 1)

Y2 = Tbl.Cells(i, 1)

X1 = Tbl.Cells(i - 1, RdColData)

X2 = Tbl.Cells(i, RdColData)

Interpolate = X1 + (X2 - X1) * (Y - Y1) / (Y2 - Y1)

Application.ScreenUpdating = True

Exit Function

HorizontalTable:

For i = 1 To Tbl.Columns.Count

'when the loop ends, the next item is Y2, the closest higher value from data table

If IsNumeric(Tbl.Cells(1, i)) And Tbl.Cells(1, i) > Y Then

Exit For

End If

Next i

Y1 = Tbl.Cells(1, i - 1)

Y2 = Tbl.Cells(1, i)

X1 = Tbl.Cells(RdColData, i - 1)

X2 = Tbl.Cells(RdColData, i)

Interpolate = X1 + (X2 - X1) * (Y - Y1) / (Y2 - Y1)

Application.ScreenUpdating = True

End Function

To use it, you should change your table to set the max limits instead of ranges:

Number of employees Number of companies

9 | 1187045 |

49 | 217240 |

249 | 35940 |

250 | 7675 |

To use it in sheet:

=IF(D2>=250,XLOOKUP(250,SampleData[Number of employees],SampleData[Number of companies],0),Interpolate(D2,SampleData,2,TRUE))

I'm attaching a more detailed file with your sample and other explanations, from <a href="https://www.excel-first.com/user-defined-function-for-linear-interpolation-in-excel/">this source</a>

1 Guest(s)