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

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.

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

2 Likes

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=720023729&range=A1:E8

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

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

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

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

1 Like

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.

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)

2 Likes

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.

  1. 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.

  2. 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.

2 Likes

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

1 Like