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