Extracting information from Table and performing equations

218 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello All, 

I'm somewhat stuck when it comes to transitioning to Airtable, so any help would be appreciated. 

I have a Table, called {Pricing} and another called {Quote}. 

My intention is to extract multiple entities from {Pricing}, perform conditional formulas and return a final price in {Quote}. The values in {Pricing} will never change, however, the requirements for what will be extracted from it will have to change. 

I have created the above in Excel, where I was relying on the FILTER formula, and VLOOKUP, I'm just looking to perform a similar task in Airtable. 

For more context, my products are Gates. And in {Pricing} I have the gate listed in a Matrix, height increments of 250mm and Width Increments of 250mm, with a price in each field. I'll try my best to illustrate.

Model&Height    - 1m          - 1.25m     - 1.5m      - 1.75m     - 2m     - 2.25m     - etc
MODEL1-1m         - £1000   - £1250    - £1500   - etc
MODEL1-1.25m    - £2000  - £2250   - £2500   - etc
MODEL1-1.5m      - £3000  - £3250   - £3500   - etc
MODEL1-Extras    - £150     -£200     -£250     - etc
MODEL1-Handles - £50      -£100     -£200     - etc

And then the entire process repeats for MODEL2, MODEL3, MODEL4, etc 

In {Quote}, I have several Single Select fields, like:

HEIGHT: (1m, 1.25m, 1.5m, 1.75m, 2m)
WIDTH: (1m, 1.25m, etc)
EXTRAS: (With, Without)
HANDLES: (With, Without)

What I want is a formula or VLOOKUP to look into the {Pricing} table and pick the coresponding model and with a formula add up the prices together. 

An example: 
HEIGHT:      1.5m
WIDTH:        1.5m
EXTRAS:      With

Would result in a formula similar to: £3500 + £250 + £200 and return the total price of: £3950.

I know how to do it for just the MODEL, however, when I try to link with 2 or more records, the values stack and I'm at a loss on how to manipulate it. 

If I need to create 2-3 additional tables for this process, I wouldn't mind at all, however, we have 250 models, each with up to 10 different unique options on top, so it would end up creating a significant amount of entries in {Pricing} that I don't want disturbed. 

Additionally, we produce on average 300-400 quotes a month and we expect that to double in the next couple of months (hence the transition to airtable), so I would like the process to be automatic once the Form in {Quote} has been filled in order to streamline the process. 

Do I create custom automations?

Any help or advice regarding this would be greatly appreciated. 

I can share images if required. 


Thank you


0 Replies 0