Forum Discussion

JROZZ's avatar
JROZZ
Copper Contributor
May 31, 2024

Spreadsheet table auto populate

Hi, I have a table that i'm entering figures into, i'd like to have the table reference a second table (ie: if i enter a value of 600, then that cell looks to the 'master table' and automatically ta...
Charting
excel
Formulas and Functions
dscheikey's avatar
dscheikey
Bronze Contributor
Jun 01, 2024

JROZZ 

Instead of reading the information from the existing list, there are two other options. With VLOOKUP() or XLOOKUP() you have the disadvantage that only the exact values that are present are found. You will not find intermediate values.


I have no idea how to convert PSI into the other values, but both seem to be linearly related. Therefore, there is also a formula with which you can calculate the values.

The factors appear to be PSI * 55.279 for Torque and PSI * 0.2377 for Load????


The second possibility with this linear relationship is to use the built-in FORECAST() function. This would look like this: (in my example, the value for PSI is in cell E3)

 

Torque
=IF(OR(E3="",E3<=0),#N/A,FORECAST(E3,{27639,99503},{500,1800}))

Load
=IF(OR(E3="",E3<=0),#N/A,FORECAST(E3,{118.848,427.858},{500,1800}))

In both variations, you no longer need your auxiliary table.

Such a simple function is ideal for creating a user-defined function with LAMBDA(). In my enclosed example, I have used the functions: TORQUE() and LOAD() with the forecast function.


Good luck!

 

Resources