Rate schedule formula based on miles and category

[David_Watson]

Hello and thank you in advance if you can help with this.
The rate chart example we use below,
I would like to be able to pick filed cat1, cat2, cat3, etc and then another field zone1, zone2, zone3, etc in another field enter the miles and have the formula / base out put the correct rate.
z1 z2 z3 z4 z5 z6 z7
cat1 20.06 26.81 35.06 1.09 1.09 1.09 1.09
cat2 27.56 34.31 44.81 1.24 1.24 1.24 1.24
cat3 $82.50 82.5 90 1.13 1.13 1.13 1.13 1.13

[Justin_Barrett]

Welcome to the community, @David_Watson! :grinning_face_with_big_eyes: Just to make sure I understand your goal clearly, let’s say someone drove 100 miles, and they choose Cat2, Zone 3, which is a rate of 44.81. The output should then be $44.81 x 100, for a total of $4481. Is that correct?

In your actual setup, how many categories and zones are there?

[David_Watson]

chart did not display correctly in first post
in the example cat 2 100 miles would be in zone 4 which is a per mile fee of 1.24 = 124

there is only 3 cat at this time, and 7 zones.
here is a link to the chart we would use" tinyurl dot com /sdm47ft" (unable to add link so had to spell it out)
Ideally I would like to have the cats, zones in a table that could be easily modified. and have a formula that looks at those and and compare “miles” field in another table and result in a field in same table as “miles”

[Justin_Barrett]

David_Watson:

in the example cat 2 100 miles would be in zone 4 which is a per mile fee of 1.24 = 124

Thanks for the clarification. That difference between flat fees vs per-mile fees is good to know, and will actually make the resulting setup easier. Here’s what I came up with. First, I made a [Rates] table:

To that table I added a formula field that concatenated all of the rate values, prefixing each one with a special marker corresponding to its zone. I’ll explain the purpose of this later. For now, here’s how the field looks, along with its formula:

"|1|" & {Zone 1} & "|2|" & {Zone 2} & "|3|" & {Zone 3} & "|4|" & {Zone 4} & "|5|" & {Zone 5} & "|6|" & {Zone 6} & "|7|" & {Zone 7}

Next I made a [Trips] table, where I entered the mileage into {Miles Driven}, and linked to a rate category from the [Rates] table.

I then added an {All Rates} rollup field to pull the mashed-together rates based on the link. (This will be hidden in later screenshots.)

I made a {Zone} field to calculate the zone based on the miles driven. Because the zones are based on multiples of 25 miles, this was fairly easy. The MIN() component ensures that the zone will never be greater than 7.

IF({Miles Driven}, MIN(ROUNDUP({Miles Driven} / 25, 0), 7))

Now that the zone has been figured out, the rate can be extracted from that mashed-up string using this formula in a {Rate} field:

IF(
    Zone,
    MID(
        {All Rates},
        FIND("|" & Zone & "|", {All Rates}) + 3,
        IF(
            Zone = 7,
            LEN({All Rates}) + 1,
            FIND("|" & (Zone + 1) & "|", {All Rates})
        ) - FIND("|" & Zone & "|", {All Rates}) - 3
    )
)

One more formula field (formatted as currency) does the final calculation:

VALUE(Rate) * IF(Zone > 3, {Miles Driven}, 1)

David_Watson:

Ideally I would like to have the cats, zones in a table that could be easily modified.

A small warning about this approach: Airtable’s “dynamic” fields (formulas, lookups, rollups, etc.) are always “live,” meaning that they will always reference whatever data currently exists. In other words, when a formula makes a calculation based on another field, that calculated value isn’t “locked.” The moment that reference value changes, so does the formula’s result.

In your case, you’re going to be linking to category records in the [Rates] table, and pulling data from those linked records. This means that if you change any values in the [Rates] table, any old trip records that point to them will also recalculate their totals based on your changes.

The most common way around this is to add a new record to the [Rates] table whenever a change must be made. Old trip records will remain linked to old rate records, and the new rate record with its updated rates will be used for all trips going forward. To make the new ones easy to find, mark the “active” rate records in some way (via a formula field, a checkbox, etc.), make a view that only shows those marked records, and set the {Category} field in [Trips] to only select records from that view.

[David_Watson]

This worked perfectly. Thank You very much.
the attention to explaining the details and very fast response was excellent.