Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Lookup a number in a sliding scale table

Topic Labels: Formulas
292 5
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi,

Link

This workbook has a sheet called ‘Test’ which has fields that include stops & mileage.

These are the x & y axis fields of the associated table ‘Sliding scale’

Y = mileage
x - stops

I am struggling to write a formula that can find the associated rate based on the number of stops & mileage.

Note, that mileage could be 112 and stops could be 73 so the formula would need to look in between nearest numbers to associate the correct row/column combo.

Answer to above should be 2.07

Can anyone help?

Please

5 Replies 5

Welcome to the Airtable community!

Your link is not working for me.

If you are trying to link to a specific record in a different table based on a combination of miles and stops, you might need to use a script instead of a formula. It is difficult to tell without more information.

Sorry, link updated. I have added some some rounded values so that there is an exact match to look for instead of looking for the closest match

You updated link gives editor permissions to whoever clicks the link.
I recommend that you change it to a read-only link so that you will not be billed for random people who click your link.

Or you could post screen captures instead. That would provide the most value to anyone who happens to read this post in the future.

Looking at the data, you cannot do this with a formula field (even with the rounded values). At least, not unless you can mathematically calculate the rate without having to use the table.

I recommend using a custom script to create the link for you.

Thanks,

Really appreciated.

Can anyone advise if they are able to help with a script?

If you are interested in hiring me to write a script, you can book a meeting with me.