Help! Is there a way to Interpolate (x, y, Known v...

Philo76 · 02-21-2020 09:55 PM

Is there a way to Interpolate (x, y, with known X value) between 2 tables?

TABLE 1 TABLE 2
X! Y1 X Value Result
100 2,500 150 3050
200 3,600
300 4,200

Any help would be appreciated.

Koichi_Tsuji

Could you elaborate your requirement bit more? What your current data schema etc.

Philo76

Basically there are two tables.

Table 1 (Pricing) contains 2 columns with Multiple Rows, (X1 = Area) ( Y1 = Cost).

Table 2 (Work Sheet) contains 2 columns (X = Known Area) & (Interpolated Cost)
for each new record. Basically Known value is a quantity amount and I need to Interpolate from the known values from table 1.

I’m try to avoid using sheets and syncing every time.

Here’s a link to my spread sheet.

https://docs.google.com/spreadsheets/d/142kNHq8VYO-wIoVYk0EST3-IBmFVEVSf_mSIi_2cut8/edit#gid=7200237...

Steve

There is no ready-made way to interpolate in AppSheet; you’d have to construct a process yourself.

Jonathan_S

Spreadsheet needs access, maybe create a copy and send a link for view only.

Philo76

Oops forgot to share the file. Try this one and you can edit it.
Just enter a value in the highlighted section.
Thank you.

Koichi_Tsuji

In your E column you have spreadsheet formula. Why dont you convert to the equivalent appsheet app formula?

Suvrutt_Gurjar

Nice expression @tsuji_koichi.

Hi @Philo76 If I may add, I also think that the extrapolation expression could be worked out in AppSheet with AppSheet expressions. However there are certain things to note.

The extraploation expression will most certainly be unwieldly. This is so because there is no uniform relationship in each step between area and cost. So extrapolation expression will need to compute for each step just the way you have calculated in the spreadsheet.
The AppSheet expression could become a bit easier if you include additional column in the input price table. The columns could be called something like say Extrapolation factor. For example for 100-200 area step in the price table , the extrapolation factor is 11, for the 200-300 area step the extrapolation is 6 and so on.

Edit: Made some changes to step 2.

Suvrutt_Gurjar

Hi @Philo76

Since the requirment was interesting, I have put together a small sample app. Please explore the app " Extrapolated Costs" mentioned below if it helps.

The app uses the concept of table referencing to pull the input parameters from the price table.
the use of referencing concept has significantly reduced the complexities of expressions. In the “Compute Price” view form, please input an area and the extrapolated cost is available in the next field.

Extrapolated Costs

Philo76

I’ve tried with no success so far. I keep running into list and number compatibility issues using Index(),In(). I’m still new leaning this platform and have spent a week trying to figure this out. I was able to do it in Filemaker creating custom & case functions, & SQL. I was hoping for some guidance to point me in the right direction.

Koichi_Tsuji

I think your spreadsheet calculation could be made far more simpler. I just converted into my way to interpolate the number from 100 to 300 range, which will return the same result as your calculation.

=ifs(and(D3>=100,D3<=200),((D3-100)/100)(3600-2500)+2500,and(D3>200,D3<=300),((D3-200)/100)(4200-3600)+3600)

mangue

I guess you can install and try “Interpolation” add-on from the Google Marketplace, it’s free.

https://workspace.google.com/u/0/marketplace/app/interpolation/47482038900

I think the function you are requiring is “interpo2”. Check the list of function.

kind regards

Help! Is there a way to Interpolate (x, y, Known value X)?